Find and Replace issues

oxidizer

New Member
Joined
Feb 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
477
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,452
Office Version
  1. 365
Platform
  1. Windows
Hi, @oxidizer Welcome to the Forum.
See if this works:
Select the range then run this macro:
VBA Code:
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
 
Solution

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
568
Office Version
  1. 365
Platform
  1. Windows
see if this works:
工作簿1.xlsx
AB
13'6''3-6
27'8.5''7-8.5
Sheet2
Cell Formulas
RangeFormula
B1:B2B1=SUBSTITUTE(REPLACE(A1,FIND("'",A1),1,"-"),"'",)
 

oxidizer

New Member
Joined
Feb 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi, @oxidizer Welcome to the Forum.
See if this works:
Select the range then run this macro:
VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,452
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,907
Messages
5,721,443
Members
422,362
Latest member
elliotpat

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