software

Hi:
Here is a situation I have a column of numbers:
A1
10325.25
125.23
1.25I 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--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=513834Try 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-Proquot;Hillquot; wrote:

gt; Hi:
gt; Here is a situation I have a column of numbers:
gt; A1
gt; 10325.25
gt; 125.23
gt; 1.25
gt;
gt;
gt; I need to split this data into columns. This has to be done in such a way
gt; that all the cents will split into the same two column, all the thousands
gt; fall in one column, all the hundreds fall in one column etc.
gt;
gt; I tried using text to column but the numbers are left aligned so I cannot
gt; get the decimal point to fall in the same column, and I do not want to use
gt; leading zeros.
gt;
gt; Can anyone help me achive this.
gt;
gt; Thanks
gt;

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:quot;quot; C1: 1 D1: 2 E1: 3 F1: 2 G1: . H1: 2 I1: 3
B2:quot; quot; C2:quot; quot; D2:quot; quot; 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:quot; quot; C4: quot; quot; D4:quot; quot; E4:quot; quot; F4: 1 G4: . H4: 5 I4: 0

And so on. quot; quot; 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

quot;Hillquot; wrote:

gt; Hi:
gt; Here is a situation I have a column of numbers:
gt; A1
gt; 10325.25
gt; 125.23
gt; 1.25
gt;
gt;
gt; I need to split this data into columns. This has to be done in such a way
gt; that all the cents will split into the same two column, all the thousands
gt; fall in one column, all the hundreds fall in one column etc.
gt;
gt; I tried using text to column but the numbers are left aligned so I cannot
gt; get the decimal point to fall in the same column, and I do not want to use
gt; leading zeros.
gt;
gt; Can anyone help me achive this.
gt;
gt; Thanks
gt;

I'm pretty sure this does what you want:

For a value (or blank) in A1:

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

G1: =IF(ISNUMBER(A1),quot;.quot;,quot;quot;)

H1: =IF((10^(5-COLUMN() 2)gt;$A1),quot;quot;,--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-Proquot;Hillquot; wrote:

gt; Ron, thanks for your reply. It gives me food for thought, but not exactly the
gt; results I am looking for.
gt;
gt; Steve the results I am looking for:
gt; Example: Column A contains the following
gt; A1: 1232.23
gt; A2: 12.41
gt; A3: 14253.36
gt; A4: 1.5
gt;
gt; I need a formula to produce the following results:
gt;
gt; B1:quot;quot; C1: 1 D1: 2 E1: 3 F1: 2 G1: . H1: 2 I1: 3
gt; B2:quot; quot; C2:quot; quot; D2:quot; quot; E2: 1 F2: 2 G2: . H2: 4 I2: 1
gt; B3: 1 C3: 4 D3: 2 E3: 5 F3: 3 G3: . H3: 3 I3: 6
gt; B4:quot; quot; C4: quot; quot; D4:quot; quot; E4:quot; quot; F4: 1 G4: . H4: 5 I4: 0
gt;
gt; And so on. quot; quot; represents blank cells. The data will never contain millions,
gt; it will always max out in the thousands. The decimal places should always
gt; fall in column G. Sorry I did not explain all of this in the start.
gt;
gt; Thanks
gt;
gt; quot;Hillquot; wrote:
gt;
gt; gt; Hi:
gt; gt; Here is a situation I have a column of numbers:
gt; gt; A1
gt; gt; 10325.25
gt; gt; 125.23
gt; gt; 1.25
gt; gt;
gt; gt;
gt; gt; I need to split this data into columns. This has to be done in such a way
gt; gt; that all the cents will split into the same two column, all the thousands
gt; gt; fall in one column, all the hundreds fall in one column etc.
gt; gt;
gt; gt; I tried using text to column but the numbers are left aligned so I cannot
gt; gt; get the decimal point to fall in the same column, and I do not want to use
gt; gt; leading zeros.
gt; gt;
gt; gt; Can anyone help me achive this.
gt; gt;
gt; gt; Thanks
gt; gt;

Ron You are truly a life saver.

Thank you, thank you!!

Hill

quot;Ron Coderrequot; wrote:

gt; I'm pretty sure this does what you want:
gt;
gt; For a value (or blank) in A1:
gt;
gt; B1: =IF((10^(5-COLUMN() 1)gt;$A1),quot;quot;,--RIGHT(INT($A1/10^(5-COLUMN() 1)),1))
gt; Copy/paste that formula across through F1.
gt;
gt; G1: =IF(ISNUMBER(A1),quot;.quot;,quot;quot;)
gt;
gt; H1: =IF((10^(5-COLUMN() 2)gt;$A1),quot;quot;,--RIGHT(INT($A1/10^(5-COLUMN() 2)),1))
gt; Copy/paste that formula to I1.
gt;
gt; Copy B1:I1
gt; Paste that range down as far as you need.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Hillquot; wrote:
gt;
gt; gt; Ron, thanks for your reply. It gives me food for thought, but not exactly the
gt; gt; results I am looking for.
gt; gt;
gt; gt; Steve the results I am looking for:
gt; gt; Example: Column A contains the following
gt; gt; A1: 1232.23
gt; gt; A2: 12.41
gt; gt; A3: 14253.36
gt; gt; A4: 1.5
gt; gt;
gt; gt; I need a formula to produce the following results:
gt; gt;
gt; gt; B1:quot;quot; C1: 1 D1: 2 E1: 3 F1: 2 G1: . H1: 2 I1: 3
gt; gt; B2:quot; quot; C2:quot; quot; D2:quot; quot; E2: 1 F2: 2 G2: . H2: 4 I2: 1
gt; gt; B3: 1 C3: 4 D3: 2 E3: 5 F3: 3 G3: . H3: 3 I3: 6
gt; gt; B4:quot; quot; C4: quot; quot; D4:quot; quot; E4:quot; quot; F4: 1 G4: . H4: 5 I4: 0
gt; gt;
gt; gt; And so on. quot; quot; represents blank cells. The data will never contain millions,
gt; gt; it will always max out in the thousands. The decimal places should always
gt; gt; fall in column G. Sorry I did not explain all of this in the start.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Hillquot; wrote:
gt; gt;
gt; gt; gt; Hi:
gt; gt; gt; Here is a situation I have a column of numbers:
gt; gt; gt; A1
gt; gt; gt; 10325.25
gt; gt; gt; 125.23
gt; gt; gt; 1.25
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; I need to split this data into columns. This has to be done in such a way
gt; gt; gt; that all the cents will split into the same two column, all the thousands
gt; gt; gt; fall in one column, all the hundreds fall in one column etc.
gt; gt; gt;
gt; gt; gt; I tried using text to column but the numbers are left aligned so I cannot
gt; gt; gt; get the decimal point to fall in the same column, and I do not want to use
gt; gt; gt; leading zeros.
gt; gt; gt;
gt; gt; gt; Can anyone help me achive this.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;

You're very welcome.

I'm glad I could help.

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

XL2002, WinXP-Proquot;Hillquot; wrote:

gt; Ron You are truly a life saver.
gt;
gt; Thank you, thank you!!
gt;
gt; Hill
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; I'm pretty sure this does what you want:
gt; gt;
gt; gt; For a value (or blank) in A1:
gt; gt;
gt; gt; B1: =IF((10^(5-COLUMN() 1)gt;$A1),quot;quot;,--RIGHT(INT($A1/10^(5-COLUMN() 1)),1))
gt; gt; Copy/paste that formula across through F1.
gt; gt;
gt; gt; G1: =IF(ISNUMBER(A1),quot;.quot;,quot;quot;)
gt; gt;
gt; gt; H1: =IF((10^(5-COLUMN() 2)gt;$A1),quot;quot;,--RIGHT(INT($A1/10^(5-COLUMN() 2)),1))
gt; gt; Copy/paste that formula to I1.
gt; gt;
gt; gt; Copy B1:I1
gt; gt; Paste that range down as far as you need.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Hillquot; wrote:
gt; gt;
gt; gt; gt; Ron, thanks for your reply. It gives me food for thought, but not exactly the
gt; gt; gt; results I am looking for.
gt; gt; gt;
gt; gt; gt; Steve the results I am looking for:
gt; gt; gt; Example: Column A contains the following
gt; gt; gt; A1: 1232.23
gt; gt; gt; A2: 12.41
gt; gt; gt; A3: 14253.36
gt; gt; gt; A4: 1.5
gt; gt; gt;
gt; gt; gt; I need a formula to produce the following results:
gt; gt; gt;
gt; gt; gt; B1:quot;quot; C1: 1 D1: 2 E1: 3 F1: 2 G1: . H1: 2 I1: 3
gt; gt; gt; B2:quot; quot; C2:quot; quot; D2:quot; quot; E2: 1 F2: 2 G2: . H2: 4 I2: 1
gt; gt; gt; B3: 1 C3: 4 D3: 2 E3: 5 F3: 3 G3: . H3: 3 I3: 6
gt; gt; gt; B4:quot; quot; C4: quot; quot; D4:quot; quot; E4:quot; quot; F4: 1 G4: . H4: 5 I4: 0
gt; gt; gt;
gt; gt; gt; And so on. quot; quot; represents blank cells. The data will never contain millions,
gt; gt; gt; it will always max out in the thousands. The decimal places should always
gt; gt; gt; fall in column G. Sorry I did not explain all of this in the start.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; quot;Hillquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi:
gt; gt; gt; gt; Here is a situation I have a column of numbers:
gt; gt; gt; gt; A1
gt; gt; gt; gt; 10325.25
gt; gt; gt; gt; 125.23
gt; gt; gt; gt; 1.25
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; I need to split this data into columns. This has to be done in such a way
gt; gt; gt; gt; that all the cents will split into the same two column, all the thousands
gt; gt; gt; gt; fall in one column, all the hundreds fall in one column etc.
gt; gt; gt; gt;
gt; gt; gt; gt; I tried using text to column but the numbers are left aligned so I cannot
gt; gt; gt; gt; get the decimal point to fall in the same column, and I do not want to use
gt; gt; gt; gt; leading zeros.
gt; gt; gt; gt;
gt; gt; gt; gt; Can anyone help me achive this.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;


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 quot;5-COLUMN() 1quot;
accordingly. So if you shift it to columns J-R, that part would have
to change to quot;14-COLUMN() 1quot; to compute the correct number of 10^4 or
10,000.Regards,

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=513834Thanks SteveG!!

quot;SteveGquot; wrote:

gt;
gt; Hill,
gt;
gt; Glad to see you got your solution. Sorry I did not respond, I took off
gt; for a long weekend before getting your post. I used the IF, LEN, CHOOSE
gt; and MID functions to get the result his AM but Ron's seems like it was
gt; less work overall. Just keep in mind that if you move your table from
gt; columns A-I then you will have to adjust the quot;5-COLUMN() 1quot;
gt; accordingly. So if you shift it to columns J-R, that part would have
gt; to change to quot;14-COLUMN() 1quot; to compute the correct number of 10^4 or
gt; 10,000.
gt;
gt;
gt; Regards,
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=513834
gt;
gt;