Keeping a column as TEXT when running a Macro

albita09

New member
Joined
Jan 30, 2014
Messages
13
Reaction score
0
Points
0
Dear all here!!

The question I have is a little bit more complicated.

A bit of background:

I have a string of numbers and characters and I have a Macro that I copy-pasted from somewhere online that REMOVES any character that is not a number.

Is a great Macro that transforms:
"-a209 03 . 1 - 22 46" to 2090312246
I can send the code on request.

However the problem comes when running the macro into that string excel transforms it DIRECTLY into a Numeric.
That is we are missing the first two, or first characters when they are zeros.

For example: "007 01 1.32.08" becomes 70113208 in stead of: 0070113208

And this is a problem.

I have tried to define the cell as text before running the macro. Fails.
I have tried to re-define the cell afterwards as text but the zeros do not appear. Fails.

My last hope is anyone of you help in:

Including in the Macro a last minute section that forces the cell to remain as text...(seems easy! but I have do not know how to program in VBA)

I have another idea, but this ones seems the easiest and most sensible one.

Thank you very much in advance for everything!!

Have a great night, day etc.. wherever you are!

A.
 
Dear Excelguru community!

I am just checking whether this message has reached to you...

I would really appreciate help in this or, if not possible, a hint where to find it.

Thank you once more for your kindness!

A.
 
If I understand this correctly, you are asking for alteration of a macro, and we need to request to see that macro.Good luck.

Best post the macro.
 
I have the feeling that it is a command we have to write before macro runs but I do not know VBA language.

I am copy-pasting the Macro so maybe you or any other in my timeframe can help me:

Sub remove()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
xOut = ""
For i = 1 To Len(Rng.Value)
xTemp = Mid(Rng.Value, i, 1)
If xTemp Like "[0-9]" Then
xStr = xTemp
Else
xStr = ""
End If
xOut = xOut & xStr
Next i
Rng.Value = xOut
Next
End Sub


THANK YOU VERY MUCH TO EVERYONE!!!!
 
Try adding

Rng.NumberFormat = "@"

right above

Rng.Value = xOut
 
THAT WAS BRILLIANT NOS!!!!!
Thank you sooooooo much!
SO MUCH!
Have a great day :)
I will, for sure! :)
 
Back
Top