Remove star from cell and text format Macro issue

Wilder1626 used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this version of your spreadsheet; used MID to get rid of the stars, and formatting to change your exponentials to numbers.

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?

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

JPIT Director

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

JPIT Director
Sorry, I had not looked at you file yet. It appears that the cells were formatted after the data was put in. That doesn't work in this scenario. You can try this macro.

Sub FixA
dim lastrow as long, tmpstr as string
lastrow = [a65536].end(xlup).row
For i = lastrow to 1 step -1
if cells(i, "A").value <> "" then tmpstr = cells(i, "A").value
cells(i, "A").value = tmpstr
Next i
End Sub
Ok thanks , let me try this.
Most Valuable Expert 2011
Top Expert 2011

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial