Spiltting numbers

[Guest]

Hi:
Here is a situation I have a column of numbers:
A1
10325.25
125.23
1.25


I need to split this data into columns. This has to be done in such a way
that all the cents will split into the same two column, all the thousands
fall in one column, all the hundreds fall in one column etc.

I tried using text to column but the numbers are left aligned so I cannot
get the decimal point to fall in the same column, and I do not want to use
leading zeros.

Can anyone help me achive this.

Thanks

 

[SteveG]

Not clear what it is you are trying to do. Do you want to separate the
cents from the whole dollar amounts? One column for the dollars and
the other for the cents? If so, use the Text to Columns, Use Delimited
as the split option (Step 1) and select Other, type in the decimal point
in the blank to the right of the Other option in step 2. If not, could
you post an example of how you want the data to look?

Steve

 

[Guest]

Try something like this:
With a number in A1
B1: =--RIGHT(INT($A1/10^(10-COLUMN()+1)),1)
Copy that formula across through M1

Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Ron, thanks for your reply. It gives me food for thought, but not exactly the
results I am looking for.

Steve the results I am looking for:
Example: Column A contains the following
A1: 1232.23
A2: 12.41
A3: 14253.36
A4: 1.5

I need a formula to produce the following results:

B1:"" C1: 1 D1: 2 E1: 3 F1: 2 G1: . H1: 2 I1: 3
B2:" " C2:" " D2:" " E2: 1 F2: 2 G2: . H2: 4 I2: 1
B3: 1 C3: 4 D3: 2 E3: 5 F3: 3 G3: . H3: 3 I3: 6
B4:" " C4: " " D4:" " E4:" " F4: 1 G4: . H4: 5 I4: 0

And so on. " " represents blank cells. The data will never contain millions,
it will always max out in the thousands. The decimal places should always
fall in column G. Sorry I did not explain all of this in the start.

Thanks

 

[Guest]

I'm pretty sure this does what you want:

For a value (or blank) in A1:

B1: =IF((10^(5-COLUMN()+1)>$A1),"",--RIGHT(INT($A1/10^(5-COLUMN()+1)),1))
Copy/paste that formula across through F1.

G1: =IF(ISNUMBER(A1),".","")

H1: =IF((10^(5-COLUMN()+2)>$A1),"",--RIGHT(INT($A1/10^(5-COLUMN()+2)),1))
Copy/paste that formula to I1.

Copy B1:I1
Paste that range down as far as you need.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Ron You are truly a life saver.

Thank you, thank you!!

Hill

 

[Guest]

You're very welcome.

I'm glad I could help.

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[SteveG]

Hill,

Glad to see you got your solution. Sorry I did not respond, I took of
for a long weekend before getting your post. I used the IF, LEN, CHOOS
and MID functions to get the result his AM but Ron's seems like it wa
less work overall. Just keep in mind that if you move your table fro
columns A-I then you will have to adjust the "5-COLUMN()+1
accordingly. So if you shift it to columns J-R, that part would hav
to change to "14-COLUMN()+1" to compute the correct number of 10^4 o
10,000.


Regards,

Stev

 

[Guest]

Thanks SteveG!!