Substitute a Character if it's in the cell at this location

[Ravey]

Hello,

I have columns of thousands of numbers and letters combined. I'm trying to do a formula that will check and see if the last 6 characters begins with a "/" character, IF so THEN Substitute it with a "-" (Hyphen).
My data has a lot of "/" (backslashes) so the formula has to check only the last six characters.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]{I have this}[/TD]
[TD]{I want this}[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hi/friends[/TD]
[TD]hi/friends[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]/99996[/TD]
[TD]-99996[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lots/of/text/here/73335[/TD]
[TD]lots/of/text/here-73335[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jon/555/55987[/TD]
[TD]jon/555-55987[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]some/text/no/numbers[/TD]
[TD]some/text/no/numbers[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I've searched for this issue on the threads and on google. I really have no idea how to even phrase the correct search terms to find what I need.

Thank you in advance.

 

[mubashiraziz]

Try this ..

=SUBSTITUTE(A1,"/","-",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))

 

[Peter_SSs]

Try this ..

=SUBSTITUTE(A1,"/","-",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))

That does not appear to produce what was described or the results shown in the {I want this} column.


@Ravey
This seems to do what you have asked.

Excel Workbook
	A	B
1	hi/friends	hi/friends
2	/99996	-99996
3	lots/of/text/here/73335	lots/of/text/here-73335
4	jon/555/55987	jon/555-55987
5	some/text/no/numbers	some/text/no/numbers
Replace

 

[ask2tsp]

Try this formula in B1 then copy down

[TABLE="width: 487"]
<colgroup><col width="487"></colgroup><tbody>[TR]
   [TD="width: 487"]=IF(LEFT(RIGHT(A1,6),1)="/",   LEFT( A1, LEN(A1)-6)&"-"&RIGHT(A1,5),A1)
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[Ravey]

Hi Peter,

Your formula worked! Unfortunately, I noticed in my data there were more "/" (backslashes) in odd places.
The backslash showed up three characters in and 4 characters in. The "/" isn't absolute only six characters from the right.

{So I Have This scenario}
/99945
/888
/9999
/WP998

{I want this}
-99945
-888
-9999
-WP998



How can I make the formula do a query of the last six characters and IF there is a "/" (Backslash) within the last six characters then make it a "-" (Hyphen)?
Thank you for your input and skill, it is appreciated. I am getting better at these "IF" formulas.

 

[Ravey]

Your formula also worked (great Excel Skill my friend)!
I found that my issue was more complex than I initially thought.

View my reply below regarding my findings in my data.

Try this formula in B1 then copy down

[TABLE="width: 487"]
<tbody>[TR]
[TD="width: 487"]=IF(LEFT(RIGHT(A1,6),1)="/",   LEFT( A1, LEN(A1)-6)&"-"&RIGHT(A1,5),A1)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

How can I make the formula do a query of the last six characters and IF there is a "/" (Backslash) within the last six characters then make it a "-" (Hyphen)?

Try
=LEFT(A1,LEN(A1)-6)&SUBSTITUTE(RIGHT(A1,6),"/","-")