Find and Replace issues

[oxidizer]

Hello. I am using Excel 365, version 2012, build 13530.20440.

I am trying to convert cells that contain, for example, 3'6'' to 3-6 or 7'8.5'' to 7-8.5. Note that the apostrophes used are both single apostrophes. I have tried using find and replace to quickly change these (there are thousands across several sheets). F&R seems to work on the single apostrophe between the two numbers, but the set after the numbers is giving me issues. I imagine it has to do with how Excel handles a single apostrophe. I want to remove the two single apostrophes altogether. I have successfully replaced them with a letter or even a word, but then cannot get the letter to be replaced with nothing. I plan on using the new format to use vlookup in another workbook so it is imparitive that the format is exactly x-x. Also, my knowledge of VBA is nonexistent, so any solution avoiding that would be preferred. Thank you.

 

[rollis13]

Do you meen that if as first operation you use F&R for 2 ' as Find: and nothing as Replace: it won't work ? and then as second operation you use 1 ' as Find: and - as Replace: .
Which is your separator for thousands ? comma or apostrophe ?

 

[Akuini]

Hi, @oxidizer Welcome to the Forum.
See if this works:
Select the range then run this macro:

Sub a1161227a()
Dim i As Long, va
va = Selection
If Selection.Cells.Count = 1 Then
    Selection = Replace(Replace(Selection, "''", ""), "'", "-")
Else
    For i = 1 To UBound(va, 1)
        va(i, 1) = Replace(Replace(va(i, 1), "''", ""), "'", "-")
    Next
    Selection = va
End If

End Sub

 

[shaowu459]

see if this works:

工作簿1.xlsx
	A	B
1	3'6''	3-6
2	7'8.5''	7-8.5
Sheet2

Cell Formulas
Range	Formula
B1:B2	B1	=SUBSTITUTE(REPLACE(A1,FIND("'",A1),1,"-"),"'",)

 

[oxidizer]

Hi, @oxidizer Welcome to the Forum.
See if this works:
Select the range then run this macro:

Sub a1161227a()
Dim i As Long, va
va = Selection
If Selection.Cells.Count = 1 Then
    Selection = Replace(Replace(Selection, "''", ""), "'", "-")
Else
    For i = 1 To UBound(va, 1)
        va(i, 1) = Replace(Replace(va(i, 1), "''", ""), "'", "-")
    Next
    Selection = va
End If

End Sub

This seems to work perfectly! Thank you! What confuses me is another issue I ran into while using F&R to make these changes, entries that resulted in, for example, 3-3 or 2-8 would change to 3-Mar or 8-Feb - even if I formatted all the cells as "text." Your macro seems to allow these changes without Excel converting it to a date. Perhaps a bug in F&R or I had something configured incorrectly?

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

Perhaps a bug in F&R or I had something configured incorrectly?

Sorry, that's beyond my knowledge.
Maybe other members could help you with an explanation about that.