Replacing Stuff

  • Thread starter Thread starter caveman.savant
  • Start date Start date
C

caveman.savant

I have text in column A that I want to Normalize. Some of the text
contains an "O" in the 2nd position. Also some of the strings have a
Space that need to be replaced with a "-". Some, but not all have
both.
(currently)
VSTYSO67-T
VSTYS077 Y
VSTYSO67 Z

(corrected to)
VSTYS067-T
VSTYS077-Y
VSTYS067-Z


To change the "O" to "0", I use
=IF(ISNUMBER(FIND("O",TRIM(MID(email28806!A2,5,LEN(email28806!
A2))))),REPLACE(email28806!A2,FIND("O",email28806!A2),1,"0"),"")

To change the " " to "-", I use
=IF(ISNUMBER(FIND(" ",TRIM(MID(email28806!A2,5,LEN(email28806!
A2))))),REPLACE(email28806!A2,FIND(" ",email28806!A2),1,"-"),"")

how can I combine these two statements?
 
If there should be no o's (oh's) in your data, then I'd drop the formulas and
just run a couple of edit|replaces:

Select the range
edit|replace
what: o (oh)
with: 0 (zero)
replace all

And
Edit|replace
what: (space character)
with: - (hyphen)
replace all

If you had to use a formula under the same conditions:

=SUBSTITUTE(SUBSTITUTE(A1,"o","0")," ","-")
 
Back
Top