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

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

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