Avatar of Wilder1626
Wilder1626
Flag 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
Microsoft Excel

Avatar of undefined
Last Comment
Wilder1626

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Andy Marshall

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Wilder1626

ASKER
Hi

is there a modification on the macro?
Wilder1626

ASKER
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
JP

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

columns.autofit
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
JP

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Wilder1626

ASKER
Ok thanks , let me try this.
Rory Archibald

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.
Wilder1626

ASKER
Perfect thanks, its all working great.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.