Text Formula Challenge

[EU]

Dear Excel Wizard:

The following formula returns a name in "First
Name"(space)"Last Name" order and I would like the return
data to come in "Last Name",(space)"First Name" order.
(the other conditions are to return "NO DATA" upon coming
across a blank cell, and returning the text in "proper"
format). How would this formula be adjusted to do that?

=(IF(A2=" ","NO DATA",(PROPER(A2))))

As well, if A2 has a text string value of "Doe,John", how
would I add a space between the comma and "John" (to make
Doe, John)?

This is stumping me!

Thanks.

EU

 

[Norman Harker]

Hi EU!

Try:

=(IF(A2="","NO DATA",(PROPER(SUBSTITUTE(SUBSTITUTE(A2," ",", "),",",",
")))))

I've amended your "NO DATA" condition from " " to "". A " " is a
single space entered in the cell whereas "" is a blank cell or a
reference to a cell that contains a formula containing a blank.

I'm not sure this will work for you as you really needed to provide a
sample of your original data to work on.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Monday: Central African Republic (National Day of
Prayer); El Salvador (Balance Day); Guatemala (Army Day); Israel (Rosh
Hodesh Tammuz); Sudan (National Salvation Revolution Day); Ukraine
(Constitution Day); Zaire (Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[EU]

Hi Norm,

Thanks for that formula. It does work, but it gives me
FirstName (comma)(space)LastName, instead of LastName
(comma)(space)FirstName.

So,if the name in cell A2 is John(space)Doe, how can I
change it to Doe(comma)(space)John?

Also, I need to build in a condition for the occasional
three name-name, such as John Eric Doe, to read Doe(comma)
(space)John Eric.

Is this even "do-able"?

Thanks!

EU

 

[Norman Harker]

Hi EU!

Like I said and regarding this and your other request from Paul. Life
is a lot easier if you give a sample of your data and what you want it
to converted to.

Four or five different samples of data plus your version of how you
want it, should be easy enough.

At present my inclination is that you should use Data > Text to
columns. Followed by concatenation of the columns with a comma and
space and with an IF function applying to an empty cell to cover three
name possibilities. Certainly seems easier than playing around with
single cell formulas for new permutations that you throw into the pot
later.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Tuesday: Belgium, Botswana, British Virgin Islands,
Burundi, Canada, China, French Polynesia, Ghana, Haiti, Hong Kong,
Rwanda, Somalia, St. Eustatius, St. Vincent & the Grenadines, Surinam,
Taiwan, Thailand.
Celebrations Tuesday: Ratha Yatra (Chariot Journey): Hinduism. Rosh
Hodesh Tammuz (last day): Judaism
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi EU!

So much easier now we have a "What you've got > What you want"
especially as there were two different lists.

First list: (Based on A1 containing the first of the list)

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1))

Second list: (Based on A8 containing the first of the list)

=LEFT(A8,FIND(",",A8))&" "&RIGHT(A8,LEN(A8)-FIND(",",A8))

These seem to work as required. Looking at earlier posts, you may want
to nest these solutions in IF functions to cover empty cells. And
there was also a case issue that you might need covering.

Again, don't hesitate to post back with further problems but note how
much easier it was when you gave a clear picture.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Wednesday: Curacao (Flag Day), Tibet (Chungbacixi
Festival)
Celebrations Wednesday: Chungbacixi Festival: Buddhism
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.