Macro to edit the current cell

Copper Contributor

I export a report from our ERP system to Excel. Our job numbers use letters to represent iterations of the job of the format 12345E-01. When this is exported to Excel, it is displayed as 1.23E+03, and when you select the cell it is displayed as 1234.5. I need to convert the number 1234.5 to text 12345E-01. Be aware that this number is only a sample; in any given report, there will be dozens of numbers to convert and multiples of each individual number. The keystrokes are easy, but when I try to record them the macro doesn't repeat the keystrokes but just copies the number I edited into the cell. Here's the code I get:

Sub E_Jobs()
' E_Jobs Macro
' Remove exponent in E jobs
' Keyboard Shortcut: Ctrl+e
ActiveCell.FormulaR1C1 = "'10947E-01"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Is there a way to record the keystrokes themselves and not the outcome of the keystrokes? Here's what I want to end up with (I don't know the VBA names of the keys, so I've just typed them in {}:


 Select cell



I'm on the current version of Excel 365. I had recorded this macro before and it worked, but apparently some update messed it up.

2 Replies
best response confirmed by Jeff_JPK (Copper Contributor)


Sub E_Jobs()
    ActiveCell.Value = "'" & Replace(ActiveCell.Value, ".", "") & "E-01"
End Sub
Thank you Hans! Works like a charm!