-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Cleaning data
- Thread starter Tracemark
- Start date
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,464
- Office Version
- 365
- Platform
- Windows
1) Does the 44 that you want to replace always appear at the beginning of the number?
2) Are the numbers that you want to change always 12 digits long?
3) Can we assume the cells your numbers are in are formatted as Text?
4) Can you make use of a macro to change the number within its own cell or are you looking for a formula to display the changed value in a different cell.
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,464
- Office Version
- 365
- Platform
- Windows
I am guessing that 44 could appear in locations where it should not be changed... hence my first question above.
Upvote
0
Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
How about you format it as international format? Use custom number format as: +00 0000 000000
To replace the "44" you could use this:
1. Select the range of telephone numbers
2. Go to the VBE (ALT+F11)
3. Go to the Immediate Window (Control+G)
4. Paste the code above
5. Hit Enter
6 Format the range as: 00000 000000
To replace the "44" you could use this:
Code:
selection.value=evaluate("if(row(),mid(" & selection.address & ",3,255))")2. Go to the VBE (ALT+F11)
3. Go to the Immediate Window (Control+G)
4. Paste the code above
5. Hit Enter
6 Format the range as: 00000 000000
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,464
- Office Version
- 365
- Platform
- Windows
Give this macro a try (change the red highlighted text to the address for your actual range of cells)...
HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Replace44With0InsertSpace) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this action again later on and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook; otherwise, just save your file as normal (the macro will be deleted from the workbook when you save it a you normally would).
Code:
Sub Replace44With0InsertSpace()
Dim R As Long, C As Long, Nums As Variant
Const RangeWithNumbers As String = "[COLOR=#ff0000][B]A1:C16[/B][/COLOR]"
Nums = Range(RangeWithNumbers)
For R = 1 To UBound(Nums)
For C = 1 To UBound(Nums, 2)
If Len(Nums(R, C)) Then
Mid(Nums(R, C), 1) = " 0"
Nums(R, C) = Format(Trim(Nums(R, C)), "00000 000000")
End If
Next
Next
Range(RangeWithNumbers) = Nums
End SubHOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Replace44With0InsertSpace) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this action again later on and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook; otherwise, just save your file as normal (the macro will be deleted from the workbook when you save it a you normally would).
Last edited:
Upvote
0
