Results 1 to 6 of 6

Thread: Keeping a column as TEXT when running a Macro

  1. #1

    Keeping a column as TEXT when running a Macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    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.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    722
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  4. #4
    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!!!!

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    722
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Try adding

    Rng.NumberFormat = "@"

    right above

    Rng.Value = xOut

  6. #6
    THAT WAS BRILLIANT NOS!!!!!
    Thank you sooooooo much!
    SO MUCH!
    Have a great day
    I will, for sure!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •