Separating words in a single cell

[ibere]

Hello there. This is my first post here, I hope this question is not too stupid, but I have no idea on how to do it...:

I have a list of names like this:
JohnSmith

and I need to turn it into this:
John Smith

Is there a way to do it? I imagine the way is to ask Excel to include a space between the capital letters, but I don't know how to do it.

Thanks!

[Ron Rosenfeld]

On Thu, 15 Jun 2006 11:11:02 -0500, ibere
<[email protected]> wrote:

>
>Hello there. This is my first post here, I hope this question is not too
>stupid, but I have no idea on how to do it...:
>
>I have a list of names like this:
>JOHNSMITH
>
>and I need to turn it into this:
>JOHN SMITH
>
>Is there a way to do it? I imagine the way is to ask Excel to include a
>space between the capital letters, but I don't know how to do it.
>
>Thanks!

How do you know that

JOHNSMITH should be JOHN SMITH and not JOHNS MITH?


--ron

[Bob Phillips]

They are all capitals. If you don't have some sort of rule, capitals, or a
space between, it is impossible.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ibere" <[email protected]> wrote in
message news:[email protected]...
>
> Hello there. This is my first post here, I hope this question is not too
> stupid, but I have no idea on how to do it...:
>
> I have a list of names like this:
> JOHNSMITH
>
> and I need to turn it into this:
> JOHN SMITH
>
> Is there a way to do it? I imagine the way is to ask Excel to include a
> space between the capital letters, but I don't know how to do it.
>
> Thanks!
>
>
> --
> ibere
> ------------------------------------------------------------------------
> ibere's Profile:
http://www.excelforum.com/member.php...o&userid=35455
> View this thread: http://www.excelforum.com/showthread...hreadid=552319
>

[ibere]

Bob, thanks.

I'm writing in the forum and I put the words in bold, so I think it turnet into all capitals in the usenet group, but all names are in the format like JohnSmith.

[Bob Phillips]

OK, try this

=LEFT(A1,MIN(IF(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))<97,ROW(INDIRECT(
"2:"&LEN(A1)))))-1)
&" "&
MID(A1,MIN(IF(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))<97,ROW(INDIRECT("2
:"&LEN(A1))))),99)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ibere" <[email protected]> wrote in
message news:[email protected]...
>
> Bob, thanks.
>
> I'm writing in the forum and I put the words in bold, so I think it
> turnet into all capitals in the usenet group, but all names are in the
> format like JohnSmith.
>
>
> --
> ibere
> ------------------------------------------------------------------------
> ibere's Profile:
http://www.excelforum.com/member.php...o&userid=35455
> View this thread: http://www.excelforum.com/showthread...hreadid=552319
>

[Ardus Petus]

Create an UDF (User Defined Function) in a module with following code:

'----------------------------------------------------
Function FirstLast(sText As String) As String
Dim re As RegExp
If re Is Nothing Then
Set re = New RegExp
re.Pattern = "([A-Z][a-z]*)([A-Z][a-z]*)"
re.IgnoreCase = False
re.Global = True
End If

FirstLast = re.Replace(sText, "$1 $2")
End Function
'---------------------------------------------------------------------

Add a reference to Microsoft VBScripr Regular Expressions 1.0

Enter following formula in worksheet:

=FirstLast(A1)

That should do it!

Cheers,
--
AP

"ibere" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> Bob, thanks.
>
> I'm writing in the forum and I put the words in bold, so I think it
> turnet into all capitals in the usenet group, but all names are in the
> format like JohnSmith.
>
>
> --
> ibere
> ------------------------------------------------------------------------
> ibere's Profile:
> http://www.excelforum.com/member.php...o&userid=35455
> View this thread: http://www.excelforum.com/showthread...hreadid=552319
>

[ibere]

Ardus,
I tried to use the code you sent, but a message saying "User-defined type not defined" appears, do you know what can I be doing wrong? Thanks!

[Ron Rosenfeld]

On Thu, 15 Jun 2006 11:59:39 -0500, ibere
<[email protected]> wrote:

>
>Bob, thanks.
>
>I'm writing in the forum and I put the words in bold, so I think it
>turnet into all capitals in the usenet group, but all names are in the
>format like JohnSmith.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr


Then use this formula:

=REGEX.SUBSTITUTE(A1,"([a-z])([A-Z])","[1] [2]")

It will place a <space> between every pair in the string that is characterized
by a non-cap letter followed by a capitalized letter.

So in addition to

JohnSmith --> John Smith

it will also do

JohnFranklinSmith --> John Franklin Smith
--ron

[Bob Phillips]

You didn't set the reference as suggested?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ibere" <[email protected]> wrote in
message news:[email protected]...
>
> Ardus,
> I tried to use the code you sent, but a message saying "User-defined
> tyo not defined" appears, do you know what can I be doing wrong?
> Thanks!
>
>
> --
> ibere
> ------------------------------------------------------------------------
> ibere's Profile:
http://www.excelforum.com/member.php...o&userid=35455
> View this thread: http://www.excelforum.com/showthread...hreadid=552319
>

[Ron Rosenfeld]

On Thu, 15 Jun 2006 11:11:02 -0500, ibere
<[email protected]> wrote:

>
>Hello there. This is my first post here, I hope this question is not too
>stupid, but I have no idea on how to do it...:
>
>I have a list of names like this:
>JOHNSMITH
>
>and I need to turn it into this:
>JOHN SMITH
>
>Is there a way to do it? I imagine the way is to ask Excel to include a
>space between the capital letters, but I don't know how to do it.
>
>Thanks!

Just as an addition to my previous post,

=REGEX.SUBSTITUTE(A1,"([A-Z])"," [1]",2)

will place a space before every capital letter except the first one.

So:

JohnSmith John Smith
JohnFranklinSmith John Franklin Smith
JohnFSmith John F Smith
JohnF.Smith John F. Smith


--ron

[Ron Rosenfeld]

On Thu, 15 Jun 2006 19:22:29 +0200, "Ardus Petus" <[email protected]>
wrote:

>Create an UDF (User Defined Function) in a module with following code:
>
>'----------------------------------------------------
>Function FirstLast(sText As String) As String
> Dim re As RegExp
> If re Is Nothing Then
> Set re = New RegExp
> re.Pattern = "([A-Z][a-z]*)([A-Z][a-z]*)"
> re.IgnoreCase = False
> re.Global = True
> End If
>
> FirstLast = re.Replace(sText, "$1 $2")
>End Function
>'---------------------------------------------------------------------

Slight modification to put a <space> before every capital except the first:

===========================================
Function FirstLast(sText As String) As String
Dim re As RegExp
If re Is Nothing Then
Set re = New RegExp
re.Pattern = "([A-Z])"
re.IgnoreCase = False
re.Global = True
End If

FirstLast = Trim(re.Replace(sText, " $1"))
End Function
==============================================
--ron

[Kathrine J Wathne]

Try using this from the menu:

Data - Text to columns
Fixed width - and then pull the line into the middle of johnsmith before you
continue. It will separate into 2 colums etc.


"ibere" <[email protected]> skrev i melding
news:[email protected]...
>
> Hello there. This is my first post here, I hope this question is not too
> stupid, but I have no idea on how to do it...:
>
> I have a list of names like this:
> JOHNSMITH
>
> and I need to turn it into this:
> JOHN SMITH
>
> Is there a way to do it? I imagine the way is to ask Excel to include a
> space between the capital letters, but I don't know how to do it.
>
> Thanks!
>
>
> --
> ibere
> ------------------------------------------------------------------------
> ibere's Profile:
> http://www.excelforum.com/member.php...o&userid=35455
> View this thread: http://www.excelforum.com/showthread...hreadid=552319
>

[eaterofjellybeans]

Hi all,

Thanks for all your work on this thread. I am trying to utilize this VBA to further edit a string field. There are exceptions to the formatting and I was wondering if anymore with more knowledge of RegEx would be able to help me include those exceptions.

For example:
I would like to make this: C5_6D_AmpleLength_12Months_toCompleteAAAOnlineClass look like:
C5_6D_Ample Length_12 Months_to Complete AAA Online Class

The code I have right now looks like:

Function FirstLast(sText As String) As String
Dim re As RegExp
If re Is Nothing Then
Set re = New RegExp
re.Pattern = "([A-Z])"
re.IgnoreCase = False
re.Global = True
End If

FirstLast = Trim(re.Replace(sText, " $1"))
End Function

Any ideas?

Thanks for your help in advance!