Spiltting numbers

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

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

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


"Hill" wrote:

> 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
>

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

"Hill" wrote:

> 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
>

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


"Hill" wrote:

> 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
>
> "Hill" wrote:
>
> > 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
> >

Ron You are truly a life saver.

Thank you, thank you!!

Hill

"Ron Coderre" wrote:

> 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
>
>
> "Hill" wrote:
>
> > 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
> >
> > "Hill" wrote:
> >
> > > 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
> > >

You're very welcome.

I'm glad I could help.

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

XL2002, WinXP-Pro


"Hill" wrote:

> Ron You are truly a life saver.
>
> Thank you, thank you!!
>
> Hill
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "Hill" wrote:
> >
> > > 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
> > >
> > > "Hill" wrote:
> > >
> > > > 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
> > > >

Hill,

Glad to see you got your solution. Sorry I did not respond, I took off for a long weekend before getting your post. I used the IF, LEN, CHOOSE and MID functions to get the result his AM but Ron's seems like it was less work overall. Just keep in mind that if you move your table from 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 have to change to "14-COLUMN()+1" to compute the correct number of 10^4 or 10,000.


Regards,

Steve