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

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

Originally Posted by 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 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