Excel Formula Help

[EricBrian]

So, I really don't want to learn Excel so was wondering if somebody could help.... I need to rewrite the following:

Eric Brian
William John Smith

These should be rewritten into:

Brian, Eric
Smith, William John

Can somebody who knows Excel help with a formula for this?

Thanks

 

[Veldek]

So, I really don't want to learn Excel so was wondering if somebody could help.... I need to rewrite the following:

Eric Brian

I want to create:

Brian, Eric

Can somebody who knows Excel help with a formula for this?

Thanks

So, do you enter "Eric Brian" in one cell and want to have "Brian, Eric" in another? Assuming that it's always the same form (i.e. no middle names), you can use a formula like that (assuming the original content is in A1):

=CONCATENATE(RIGHT(A1;LEN(A1)-FIND(" ";A1));", ";LEFT(A1;FIND(" ";A1)-1))

I'm not sure about the English formula names as I use the German version, but it should help you nevertheless.

 

[EricBrian]

So, do you enter "Eric Brian" in one cell and want to have "Brian, Eric" in another? Assuming that it's always the same form (i.e. no middle names), you can use a formula like that (assuming the original content is in A1):

=CONCATENATE(RIGHT(A1;LEN(A1)-FIND(" ";A1));", ";LEFT(A1;FIND(" ";A1)-1))

I'm not sure about the English formula names as I use the German version, but it should help you nevertheless.

Oh, I forgot to mention that this is Excel for mac and not windows. Does this matter? I ask because this does not work. Also, "Eric Brian" is in one cell.

Thanks!

 

[EricBrian]

I modified that formula somewhat and came up with this:

=RIGHT(E1,LEN(E1)-FIND(" ",E1)) & ", " & LEFT(E1,FIND(" ",E1)-1)

It worked nicely.

Thank you very much!

 

[Veldek]

I modified that formula somewhat and came up with this:

=RIGHT(E1,LEN(E1)-FIND(" ",E1)) & ", " & LEFT(E1,FIND(" ",E1)-1)

It worked nicely!

Ok, so it seems Excel for Mac doesn't know the concatenate function and uses "," instead of ";". Glad you found a way around it and it works.

 

[EricBrian]

Veldek, one more thing... Sometime I only have a first name so now I get a #VALUE! error. Is there a way to avoid this? Thanks.

 

[miles01110]

I would just do it in steps.

Do "Text to Columns" on your data with a space as the delimiter.

Then to get the names in reverse order, do something like

=IF(ISBLANK(B1)=TRUE, A1, CONCATENATE(B1, ", ",A1))

So if the second cell (the last name) is empty then it just puts the first name. If there is a string in each cell then it will format your text how you want it. This will also avoid the #VALUE error you get.

 

[Veldek]

Veldek, one more thing... Sometime I only have a first name so now I get a #VALUE! error. Is there a way to avoid this? Thanks.

You can try to use a function that gives you a number value for an error you you receive. My translation of this function would be ERROR.TYPE. It's the FIND formula which can't find a blank and then returns this error. Use an IF function to see if the FIND function gives you an error and control if you can use this formula or another one when there's only one given name.