space in begining of cell

[l0rtsu]

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

 

[Mark W.]

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

 

[l0rtsu]

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

 

[Yogi Anand]

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.

 

[Juan Pablo González]

Try with

=CODE(A1)

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

 

[l0rtsu]

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.

 

[Yogi Anand]

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!

 

[Mark W.]

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