Replace a certain Character in a column

  • Thread starter Thread starter BeeNeeB
  • Start date Start date
B

BeeNeeB

To make a long story short, I have a lot of serial numbers (for example
SWGMM036478, and I need to remove all of the "S" character
automatically. Is there anyway that I can do this easily in Excel.
have over 600 serial numbers and they all have the letter "S" at th
beginning and they are not supposed to be there. Any tips? Thanks
 
Select the cells that contain the serial numbers.
Choose Data>Text to Columns
Select Fixed Width, click Next
In the Data Preview window, click after the S,
to add a break line.
Click Next
With the first column selected, choose
'Do not import column (skip)'
Click Finish
 
Hi
one way: Enter the following formula in the adjacent column
=SUBSTITUTE(A1,"S","")
(if column A stores your data) and copy this formula for all rows
after this you may copy this new column and insert it again as values
to replace the formulas (goto 'Edit - Paste Special' and choose
'Values')
 
Even another option
Use the Edit Find & Replace
Select Edit, select the Replace Tab (ensure that the Match Entire Cell Contents is not selected). Insert S in the Find What cell then select Replace All

Stephen R.
 
As long as there potentially no other S's in the data :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Stephen R. said:
Even another option:
Use the Edit Find & Replace.
Select Edit, select the Replace Tab (ensure that the Match Entire Cell
Click to expand...
Contents is not selected). Insert S in the Find What cell then select Replace
All.
 
~× said:
*Even another option:
Use the Edit Find & Replace.
Select Edit, select the Replace Tab (ensure that the Match Entire
Cell Contents is not selected). Insert S in the Find What cell then
select Replace All.

Stephen R. *
Click to expand...

That worked perfect! Thanks!
 
Back
Top