How to strip away anything beyond a specific character:

Wulf

Active Member
Joined
Dec 1, 2004
Messages
395
Office Version
  1. 365
Platform
  1. Windows
Example:
Aztec Steel - Coil Springs ~ Advanced Reporting

Thousands of entries exist with this '~' behind the numerous companies in this column list, indicating the title of a submitted help ticket.

'Ctrl' 'H' will get me the find and replace, but how do I strip away EVERYTHING past the '~', including the '~', all the way down the column, quickly?

Thank you.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,630
Insert an empty column to the right of the column with your data. Go to the Data tab, click Text to Columns, check delimited > Next > uncheck everything > check Other, and put a ~ in the box. Click Finish. Now just delete the new column.

Hope this helps.
 
Upvote 0

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,724
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Assuming your data is in column A starting in row 1, place this formula in B1 and copy it down as far as you need it: =LEFT(A1,(FIND("~",A1,1)-1))
 
Upvote 0

Forum statistics

Threads
1,191,394
Messages
5,986,352
Members
440,018
Latest member
GolfOne

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