Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Columns
I need to break up a column of text that holds a combination of strings and numbers. The problem is that the lines of text are not uniform. For instance: John Doe 123 23 Los Angeles California James E Smith 23 1234 Austin Texas How can I break up the text into columns as if it the text was comma delimited like this: John Doe, 123, 23, Los Angeles, California James E Smith, 23, 1234, Austin, Texas Thanks. -- ericsh ------------------------------------------------------------------------ ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149 View this thread: http://www.excelforum.com/showthread...hreadid=537544 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Columns
i wrote this for another post and adapted it so it may be of use to you. you can
check the newsgroup for a topic help to a very special split for other ideas i assumed all of you data was in column A watch the word wrap from outlook express Sub test2() Dim i As Long, j As Long, n As Long, z As Long, y As Long For y = 1 To 2 For i = 1 To Len(Range("a" & y).Value) If Asc(Mid(Range("a" & y).Value, i)) = 48 And Asc(Mid(Range("a" & _ ' split line here y).Value, i)) <= 57 Then Range("E" & y).Value = Left(Range("a" & y).Value, i - 1) j = i Exit For End If Next For z = 1 To Len(Range("a" & y).Value) If Asc(Right(Range("a" & y).Value, z)) = 48 And Asc(Right(Range("A" & _ 'split line here y).Value, z)) <= 57 Then Range("g" & y).Value = Right(Range("a" & y), z - 1) n = z Exit For End If Next Range("f" & y).Value = Mid(Range("A" & y), j, Len(Range("a" & y)) - (j - 1 + n _ 'split line here - 1)) Next End Sub -- Gary "ericsh" wrote in message ... I need to break up a column of text that holds a combination of strings and numbers. The problem is that the lines of text are not uniform. For instance: John Doe 123 23 Los Angeles California James E Smith 23 1234 Austin Texas How can I break up the text into columns as if it the text was comma delimited like this: John Doe, 123, 23, Los Angeles, California James E Smith, 23, 1234, Austin, Texas Thanks. -- ericsh ------------------------------------------------------------------------ ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149 View this thread: http://www.excelforum.com/showthread...hreadid=537544 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Columns
Thanks Gary, this is just what I needed. -- ericsh ------------------------------------------------------------------------ ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149 View this thread: http://www.excelforum.com/showthread...hreadid=537544 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Linking text columns with text and data columns | Excel Worksheet Functions | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |