Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Coloumns, by consecutive capital letters
Is it possible to seperate a coloumn of data to individual coloumns by using a delimiter of consecutive capital letters or where a capital letter exists in a line of text: eg. JohnSmith -- | John | Smith | eg. LondonEngland -- | London | England | -- paperclip ------------------------------------------------------------------------ paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219 View this thread: http://www.excelforum.com/showthread...hreadid=545417 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Coloumns, by consecutive capital letters
I'm sure you could loop through each cell looking for upper case characters, but
I think I'd just bite the bullet and do 26 edit|replaces. A -- |A B -- |B .... Z -- |Z Using a macro would make it less painful: Option Explicit Sub testme() Dim myRng As Range Dim lCtr As Long Set myRng = Worksheets("sheet1").Range("a:a") For lCtr = Asc("A") To Asc("Z") myRng.Replace what:=Chr(lCtr), _ replacement:="|" & Chr(lCtr), _ lookat:=xlPart, _ searchorder:=xlByRows, _ MatchCase:=True Next lCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Then you can run Data|Text to columns (and choose to ignore the first field???). paperclip wrote: Is it possible to seperate a coloumn of data to individual coloumns by using a delimiter of consecutive capital letters or where a capital letter exists in a line of text: eg. JohnSmith -- | John | Smith | eg. LondonEngland -- | London | England | -- paperclip ------------------------------------------------------------------------ paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219 View this thread: http://www.excelforum.com/showthread...hreadid=545417 -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Coloumns, by consecutive capital letters
Thats not a bad idea at all Dave, many thanks. Dave Peterson Wrote: I'm sure you could loop through each cell looking for upper case characters, but I think I'd just bite the bullet and do 26 edit|replaces. A -- |A B -- |B .... Z -- |Z Using a macro would make it less painful: Option Explicit Sub testme() Dim myRng As Range Dim lCtr As Long Set myRng = Worksheets("sheet1").Range("a:a") For lCtr = Asc("A") To Asc("Z") myRng.Replace what:=Chr(lCtr), _ replacement:="|" & Chr(lCtr), _ lookat:=xlPart, _ searchorder:=xlByRows, _ MatchCase:=True Next lCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Then you can run Data|Text to columns (and choose to ignore the first field???). paperclip wrote: Is it possible to seperate a coloumn of data to individual coloumns by using a delimiter of consecutive capital letters or where a capital letter exists in a line of text: eg. JohnSmith -- | John | Smith | eg. LondonEngland -- | London | England | -- paperclip ------------------------------------------------------------------------ paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219 View this thread: http://www.excelforum.com/showthread...hreadid=545417 -- Dave Peterson -- paperclip ------------------------------------------------------------------------ paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219 View this thread: http://www.excelforum.com/showthread...hreadid=545417 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How do I change a column of cells to all capital letters? | Excel Worksheet Functions | |||
Capital Letters | Excel Worksheet Functions | |||
Auto change font to 'capital letters' | Excel Worksheet Functions | |||
How can I write in a text in a cell using numbers and the letters. | Excel Discussion (Misc queries) |