Add characters between lower case and upper case letters

**Johnf42** · 08-26-2009, 07:09 PM

I have a string of names that run together without spaces or commas between each name.

"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.

Is there a way to add a comma and space between a lower case and upper case letter?

**martindwilson** · 08-26-2009, 07:15 PM

at a guess i'd say no!
unless someone knows how to parse it using vba

**Johnf42** · 08-26-2009, 07:25 PM

Thanks for your reply.

**shg** · 08-26-2009, 07:29 PM

If you first use text to columns to separate by spaces, then on the result, use this ugly formula:

=LEFT(A3, MATCH(TRUE, CODE(MID(A3, ROW(INDIRECT("2:" & LEN(A3)) ), 1) ) <= 90, 0)) & ", " & MID(A3, MATCH(TRUE, CODE(MID(A3, ROW(INDIRECT("2:" & LEN(A3)) ), 1) ) <= 90, 0) + 1, 99)

The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

**martindwilson** · 08-26-2009, 07:37 PM

**Johnf42** · 08-26-2009, 07:48 PM

Wow, that's a monster of a formula. I appreciate the time you took to post it, your formula skills are far beyond mine.

Is the formula cell dependent, or can I apply it to any given cell in the spreadsheet? I got a number of various results that didn't relate to the cells that I applied it to.

**shg** · 08-26-2009, 07:50 PM

It can be applied to any cell, but note there are six references, and they all have to change.

**Johnf42** · 08-27-2009, 12:58 PM

I assume that A3 is the reference, correct?

I changed the reference cell and pasted the entire formula into the formula bar on top, but it's not working. Am I entering the formula in the wrong spot?

Thanks

**IndianaJones** · 01-18-2014, 07:37 AM

Long live The Great State of Texas !

Dear sir can you please tell me how to adapt your formula to put a space(or any distinctive character like '|' , '*', etc.) on a Excel string who has this form: 'AbcDefgHijklmnopRs'.
The result I need will look like this:'Abc Defg Hijklmnop Rs'.

I am using Excel 2003 (but if you can please tell me also for 2010)

Thank you !

**Pepe Le Mokko** · 01-18-2014, 07:57 AM

Wrong canned reply

**IndianaJones** · 01-18-2014, 11:07 AM

Originally Posted by Pepe Le Mokko

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Regarding to the rule you mentioned: I have no code to post, as I said initially I asked about a modification of an EXCEL formula posted above, not VBA.
Thank you for your contribution.

**Pepe Le Mokko** · 01-18-2014, 12:56 PM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Add characters between lower case and upper case letters

LinkBack

Thread Tools

Rate This Thread

Display

Add characters between lower case and upper case letters

re: Add characters between lower case and upper case letters

re: Add characters between lower case and upper case letters

re: Add characters between lower case and upper case letters

re: Add characters between lower case and upper case letters

re: Add characters between lower case and upper case letters

re: Add characters between lower case and upper case letters

Re: Add characters between lower case and upper case letters

Re: Add characters between lower case and upper case letters

Re: Add characters between lower case and upper case letters

Re: Add characters between lower case and upper case letters

Re: Add characters between lower case and upper case letters

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions