SOLVED

Excel Text

%3CLINGO-SUB%20id%3D%22lingo-sub-3023830%22%20slang%3D%22en-US%22%3EExcel%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3023830%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20that%20contains%20the%20Full%20Name%2C%20such%20as%20Matthew%20Groff.%20I%20want%20to%20change%20the%20Full%20Name%20column%20to%20read%20Groff%2C%20Matthew%3C%2FP%3E%3CP%3EI%20wrote%20the%20formula%20%3DMID(B8%2CFIND(%22%20%22%2CB8)%2B1%2C100)%26amp%3B%E2%80%9D%2C%E2%80%9D%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20and%20this%20gives%20me%20Groff%2C%3C%2FP%3E%3CP%3EI%20wrote%20the%20formula%20%3DLEFT(B8%2CFIND(%22%20%22%2CB8)-1)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20and%20this%20gives%20me%20Matthew%3C%2FP%3E%3CP%3EI%20tried%20to%20combine%20them%20with%20%3DMID(B8%2CFIND(%22%20%22%2CB8)%2B1%2C100)%26amp%3B%E2%80%9D%2C%E2%80%9D%26amp%3BLEFT(B8%2CFIND(%22%20%22%2CB8)-1)%3C%2FP%3E%3CP%3EBut%20this%20gives%20me%20a%20Name%20error%20that%20I%20can't%20figure%20out.%20I%20have%20attached%20my%20sample%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3023830%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3023966%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3023966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1002259%22%20target%3D%22_blank%22%3E%40marty007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMID(A3%2CFIND(%22%20%22%2CA3)%2B1%2C100)%26amp%3B%22%3B%22%26amp%3BLEFT(A3%2CFIND(%22%20%22%2CA3)-1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%26nbsp%3B%3CSPAN%3E%26amp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%E2%80%9D%3C%2FFONT%3E%2C%3CFONT%20color%3D%22%23FF0000%22%3E%E2%80%9D%3C%2FFONT%3E%26amp%3B%20to%20%26amp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E%3B%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E%26amp%3B%20and%20it%20works%20in%20my%20spreadsheet.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3023972%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3023972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1002259%22%20target%3D%22_blank%22%3E%40marty007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMID(A3%2CFIND(%22%20%22%2CA3)%2B1%2C100)%26amp%3B%22%3B%22%26amp%3BLEFT(A3%2CFIND(%22%20%22%2CA3)-1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%26nbsp%3B%3CSPAN%3E%26amp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%E2%80%9D%3C%2FFONT%3E%2C%3CFONT%20color%3D%22%23FF0000%22%3E%E2%80%9D%3C%2FFONT%3E%26amp%3B%20to%20%26amp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E%3B%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E%26amp%3B%20and%20it%20works%20in%20my%20spreadsheet.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3024107%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3024107%22%20slang%3D%22en-US%22%3EThis%20did%20not%20work%20in%20my%20spreadsheet.%20Not%20sure%20why.%3C%2FLINGO-BODY%3E
Contributor

I have a column that contains the Full Name, such as Matthew Groff. I want to change the Full Name column to read Groff, Matthew

I wrote the formula =MID(B8,FIND(" ",B8)+1,100)&”,”      and this gives me Groff,

I wrote the formula =LEFT(B8,FIND(" ",B8)-1)            and this gives me Matthew

I tried to combine them with =MID(B8,FIND(" ",B8)+1,100)&”,”&LEFT(B8,FIND(" ",B8)-1)

But this gives me a Name error that I can't figure out. I have attached my sample file.

 

6 Replies
best response confirmed by marty007 (Contributor)
Solution

@marty007 

=MID(A3,FIND(" ",A3)+1,100)&";"&LEFT(A3,FIND(" ",A3)-1)

 

I changed &,& to &";"& and it works in my spreadsheet.

This did not work in my spreadsheet. Not sure why.

@marty007  The problem is your curly quotes.  Look at the difference in the quotes around the comma (curly style) compared to the rest of the quotes:

mtarler_0-1638388039537.png

Fix those quotes and you will be good.

I did that and it worked. I have no idea how I created those "wrong way" quotes. They don't appear on my keyboard. Thanks.
I'm happy it is fixed for you. As for how, by any chance did you copy and paste from MS Word or something?
In fact, I did. I was never aware that some programs create different types of quote marks. I will always remember to check from now on. Thanks again.