Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Remove star from cell and text format Macro issue

Hello all

I have this file where in column A i have numbers that don't show the good format even it it is text"

Ex: i can have 2.01111E+11 instead of 201108000000

And also, in column D i have numbers where i just want to remove the 2 starts, one at the beginning and the one at the end.

ex: *00010893003M*

How can i do that?

Thanks for your help.
test-file-1.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Andy Marshall
Andy Marshall
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wilder1626

ASKER

Hi

is there a modification on the macro?
So for the  stars to remove, i can do this:

   LastRow = [A65536].End(xlUp).Row
For i = LastRow To 4 Step -1
If Cells(i, 4) <> "" Then
Cells(i, 4) = Mid(Cells(i, 4), 2, 12)
End If
Next i

Open in new window


But about the formatting of the column A, how can i do it?

Thanks
If the data in Column A is correct and it just doesn't fit you can add this line to your macro.

columns.autofit
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok thanks , let me try this.
you can just format column A as Number with no decimal places. to get rid of the * you can just do a Replace operation (Ctrl+H) and enter:
Find: ~*
Replace: leave blank
then press Replace All.
Perfect thanks, its all working great.