formatting text with comma 4

[OCM]

Hi,

In my excel sheet I have a field called Name to include last name, first name and middle initial. Currently, is displays as below:

LastName 12 spaces FirstName 9 spaces Middle initials (example below)
Brown Charles M
Smith Lisa P

I would like to add a comma between LastName and FirstName, delete extra spaces in between as follows:

LastName, FirstName MI
Eg. Brown, Charles M
Smith, Lisa P

How can I accomplish this?

TIA,

Regards


OCM

 

[mintjulep]

Easiest would be Text-to-columns, space delimiter treating consecutive delimiters as one.
Then build back up with concatination.

 

[SkipVought]

mintjulep’s suggestion of SPACE delimiters will work, as long as EACH last name or first name contains no SPACE.

Otherwise parse Data > Text to columns as FIXED.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

If:
- it is a cell => cell conversion,
- you can add helper column with formula and next copy-paste values,
then use formula (assumed input text in A1): [tt]=SUBSTITUTE(TRIM(A1)," ",",",1)[/tt]

combo

EDIT:
If you need space after comma, replace first space by space with comma: [tt]=SUBSTITUTE(TRIM(A1)," ",", ",1)[/tt]

 

[Andrzejek]

It is also possible that you are getting [tt]LastName, FirstName[/tt], and [tt]Middle[/tt] from some kind of data base (do you?)
If so, get it in the format you desire, no conversion needed.


---- Andy

There is a great need for a sarcasm font.

 

[OCM]

Hi,

Thanks everyone for the useful suggestions to resolve the formatting problem.

It’s working very well now.

Regards,


OCM

 

[Andrzejek]

So which suggestion did you use?
How did you solve your issue?

Please share so others having the same problem may find the solution.


---- Andy

There is a great need for a sarcasm font.

 

[OCM]

Absolutely,

Step1 --> Parse data (split columns into multiple columns)
In my case: column A2=Last Name, B2=FirstName and C2=MI
Step2--> Data” tab > “Text to Columns.
Step3--> Choose Delimited
Step2--> Choose space as delimited
Step2--> Choose General
Step2--> Click Finish
Step2--> Apply the following formula in Column D2
=CONCATENATE(A2,","," ",B2," ",C2)

I hope this helps!

Regards,


OCM

 

[Andrzejek]

So you took mintjulep's suggestion, or Skip's suggestion, or both.
Don't you this someone deserves a Star?


---- Andy

There is a great need for a sarcasm font.

 

[OCM]

Andy,
I took everyone's suggestions (including yours). Sorry,how do I go about giving a star to everyone who replied with useful answers to my questions?

Regards,


OCM

 

[OCM]

I'm not sure if I did it right, I clicked 'Great post' and confirmed for giving a star.

Regards,

OCM