Text to Coloumns, by consecutive capital letters

[paperclip]

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 |

 

[Dave Peterson]

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]

Thats not a bad idea at all Dave, many thanks.

I'm sure you could loop through each cell looking for upper cas
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 intr
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Then you can run Data|Text to columns (and choose to ignore the firs
field???).



http://www.excelforum.com/showthread.php?threadid=545417