Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
ibere
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
ibere
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.misc
ibere
 
Posts: n/a
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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



  #10   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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


  #11   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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
  #12   Report Post  
Posted to microsoft.public.excel.misc
Kathrine J Wathne
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM
How to copy single cell into cell that is merged from two cells? Rod Excel Discussion (Misc queries) 3 January 22nd 06 10:24 PM
How Do? Take two words in cell 1 and slpit them to cell 2 & 3 jermsalerms Excel Discussion (Misc queries) 13 January 6th 06 07:55 PM
Help creating single cell Pop-up Bill Excel Worksheet Functions 2 June 23rd 05 05:21 PM
Pasting Word table cell with paragraph markers into single Excel c Steve Excel Discussion (Misc queries) 1 June 16th 05 11:26 PM


All times are GMT +1. The time now is 06:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"