Tab in a text string

Fooser

Board Regular
Joined
Sep 16, 2005
Messages
152
I have a problem with some text strings I recieve. They have a tab before the last initial of a name and I can't figure out how to get rid of it without losing the last letter. The text looks like this when pasted into the text box, but I see a "square" in excel signifying a tab. Does anyone know how to remove this without going through each one individually? I tried using Data, Text to Columns, Delimited without sucess. The tab delimiter causes the last letter to not show up. The only other way I can think of solving this issue is to pull the last letter then add it again after the tab delimited function. Any suggestions would be appreciated.

Example.
"JOHN, DOE
J"
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Have you tried the CLEAN worksheet function?
 

Fooser

Board Regular
Joined
Sep 16, 2005
Messages
152
The CLEAN function gets rid of the tab, but it also leaves no space so the last initial is combined with the last name. I think I can probabl do some more manipulation to the text to get what I want with this function. Thanks.
 

Fooser

Board Regular
Joined
Sep 16, 2005
Messages
152
Figured it out.

=SUBSTITUTE(B6,"
"," ")

Just copy the tab instance and pasted it in a substitute command.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top