Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: space in begining of cell

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First of all, I know that there are already topic of same problem but I couldnt find help from it and I am really newbie in VBA, I started "programmin" with this today.

    I am doing small excel "database" which has three columns and I dont want to remove manually spaces for begining of every cell in two of the rows. Information comes from web-site so cant remove spaces from that way. I am using Office Xp so Excel is 2002. What sorta macro should I use?

    btw, I cannot use Trim-function since those cells must be formated as "text" because otherwise zeros wouldnt stay in phone column.

    Mika

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    btw, I cannot use Trim-function since those cells must be formated as "text" because otherwise zeros wouldnt stay in phone column.

    Mika
    You lost me... =TRIM(" 006") produces "006".

    Also, knowing that your data is sourced from a web page keep in mind that what looks like spaces may be a non-printable ASCII value instead.

    [ This Message was edited by: Mark W. on 2002-04-19 16:14 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You lost me... =TRIM(" 006") produces "006".
    [ This Message was edited by: Mark W. on 2002-04-19 16:14 ]
    No it doesnt (at least for me...) if cell is formated to "text". I just tested it, if cell type is "text" and you put '=TRIM( 123123)' it stays that way but if you change cell type example to "general" it changes to "123123"


    [ This Message was edited by: l0rtsu on 2002-04-19 16:31 ]

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 16:30, l0rtsu wrote:
    You lost me... =TRIM(" 006") produces "006".
    [ This Message was edited by: Mark W. on 2002-04-19 16:14 ]
    No it doesnt (at least for me...) if cell is formated to "text". I just tested it, if cell type is "text" and you put '=TRIM( 123123)' it stays that way but if you change cell type example to "general" it changes to "123123"


    [ This Message was edited by: l0rtsu on 2002-04-19 16:31 ]
    Hi Mika:
    What you are saying is not clear. Trim function is used to clear the extra space from a TEXT string.
    Chances are if TRIM function is not removing the extra spaces, then as Mark stated above, it may be a non-printable character (and not necessarily a space) that you are not able to remove.
    Please post a sample of the entry from where you are trying to remove a character or a non-character as the case may be.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try with

    =CODE(A1)

    to get the ASCII number. If its 32, then it is a space, if not, it's a "strange" character.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for replying so fast and it is space, Code returned 32. I also checked it by turning one cell to general type and =trim(A5) parsed it correctly. And I cannot turn them all to General because then phone numbers wouldnt be listed correctly.

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 16:59, l0rtsu wrote:
    Thanks for replying so fast and it is space, Code returned 32. I also checked it by turning one cell to general type and =trim(A5) parsed it correctly. And I cannot turn them all to General because then phone numbers wouldnt be listed correctly.
    Are you saying you have 1 or more 0s in the beginning of the entry, and you are losing those 0s after you TRIM the original string -- I don't see why that has to be so, but in any event you can then reformat it as Text using

    =TEXT(A1,"0000000") adjusting the number of 0s to suit

    Please post back if it helps ... otherwise explain a little further and let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 16:59, l0rtsu wrote:
    Thanks for replying so fast and it is space, Code returned 32. I also checked it by turning one cell to general type and =trim(A5) parsed it correctly. And I cannot turn them all to General because then phone numbers wouldnt be listed correctly.
    Just select your cells containing phone numbers formatted as text, choose the Edit | Text to Columns... menu command, and follow these steps...

    1. At Step 1 of 3 of the wizard, choose the "Fixed width" data type and press [ Next> ]
    2. At Step 2 of 3 click in the data preview window on the 1st unit of the ruler and press [ Next> ]
    3. At Step 3 of 3 format the 1st (single character) column as "Do not import column (Skip)". Select the 2nd column (containing your phone number) and format as "Text". Press [ Finish ].

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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