Separating words in a single cell
 

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

Separating words in a single cell
 

On Thu, 15 Jun 2006 11:11:02 -0500, ibere
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

Separating words in a single cell
 

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" wrote in
message ...

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

Separating words in a single cell
 

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

Separating words in a single cell
 

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" a écrit dans le
message de news: ...

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

Separating words in a single cell
 

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" wrote in
message ...

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

Separating words in a single cell
 

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

Separating words in a single cell
 

On Thu, 15 Jun 2006 11:59:39 -0500, ibere
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

Separating words in a single cell
 

You didn't set the reference as suggested?

--

HTH

Bob Phillips

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

"ibere" wrote in
message ...

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

Separating words in a single cell
 

On Thu, 15 Jun 2006 11:11:02 -0500, ibere
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

Separating words in a single cell
 

On Thu, 15 Jun 2006 19:22:29 +0200, "Ardus Petus"
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

Separating words in a single cell
 

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" skrev i melding
...

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