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"
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,899
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top