Spread data and keep format

[XP]

I have some strings in column A that appear like:

123.123.1234.123456.123.12.123
000.000.0000.000000.000.00.000
123.456.0000.987654.444.00.222

They all follow the same pattern. I want to keep column A intact, but spread
the values across seven columns to the right. Like so (the pipes below denote
columns):

B C D E F G H
123 | 000 | 1234 | 123456 | 999 | 00 | 95

Note that I need to keep the requisite number of zeros after the spread, so
text-to-columns fails, since '000' translates as '0'.

I would like a speadsheet function and/or VBA code that would do this
correctly. Many thanks for your assistance.

 

[Gord Dibben]

Data>Text to Columns will not fail when done correctly.

Select the range in column A

Data>Text to Columns>Delimited>Other enter a period>Next>Select all columns by
Shift + Click and Column Data Format>Text

Destination....select a cell...say B1........ and Finish


Gord Dibben MS Excel MVP

 

[Ken Johnson]

I have some strings in column A that appear like:

123.123.1234.123456.123.12.123
000.000.0000.000000.000.00.000
123.456.0000.987654.444.00.222

They all follow the same pattern. I want to keep column A intact, but spread
the values across seven columns to the right. Like so (the pipes below denote
columns):

B C D E F G H
123 | 000 | 1234 | 123456 | 999 | 00 | 95

Note that I need to keep the requisite number of zeros after the spread, so
text-to-columns fails, since '000' translates as '0'.

I would like a speadsheet function and/or VBA code that would do this
correctly. Many thanks for your assistance.

In B1 use the formula...

="." & A1

filled down to add a "." to the start of each string.
Then use Edit|Replace to replace each "." with ".a"
Then use Data|Text to Columns with "." as the delimiter
This produces the correct result except they all have "a" as a prefix.
Use =MID(B1,2,LEN(B1)-1) filled across for a total of 7 columns then
down the sheet to get rid of the "a" prefix.

When I did this the 0000s stayed.

Ken Johnson

 

[Ken Johnson]

I wish I had seen Gord's before I submitted my crap-\

Ken Johnson