Spacing problems

[Heine]

Hello everybody,

I am importing a phonelist via a web query. my phone numbers look like
this when imported:

11 22 33 44

I want to remove the spaces and only return the last four digits - how
is this done most easily?

Is it possible to Vlookup in a web query qithout problems?

Thanks in advance.

Regards
Heine

 

[Guest]

With number in A1 try

=RIGHT(TRIM(SUBSTITUTE(A1,CHAR(32),"")),4)

Mike

 

[Heine]

With number in A1 try

=RIGHT(TRIM(SUBSTITUTE(A1,CHAR(32),"")),4)

Mike











- Vis tekst i anførselstegn -

Thanks Mike - that works great. Now I want to lookup in my query. Why
does it return #N/A? If I copy and insert values it does work. But
then I do not get the automation.... Hope somebody can help solve
this.

Regards
Heine

 

[Guest]

Heine,

Please post you lookup formula and an example of how your data are laid out.

Mike

 

[Heine]

Heine,

Please post you lookup formula and an example of how your data are laid out.

Mike







- Vis tekst i anførselstegn -

Hi Mike

=VLOOKUP(B2;3;Sheet1!$J$3:$L$135;FALSE)

It is a straightforward lookup. But the lookup area is a web query.
Moreover I have added a column to the lookup area where I have
calculations (the above right formula). It returns #N/A. I have also
tried to use a named area - still returns #N/A. any thoughts?

Best regards
Heine

 

[vezerid]

Hi Mike

=VLOOKUP(B2;3;Sheet1!$J$3:$L$135;FALSE)

It is a straightforward lookup. But the lookup area is a web query.
Moreover I have added a column to the lookup area where I have
calculations (the above right formula). It returns #N/A. I have also
tried to use a named area - still returns #N/A. any thoughts?

Best regards
Heine

The arguments are in the wrong order:

=VLOOKUP(B2;Sheet1!$J$3:$L$135;3;FALSE)

Also, what values do you now have in J3:J135? Your original phones or
the result of applyiung Mike's formula?

HTH
Kostis Vezerides

 

[Dave Peterson]

If you want to match up using numbers--not text that looks like numbers, try
this:

=--RIGHT(TRIM(SUBSTITUTE(A1,CHAR(32),"")),4)
or
=--RIGHT(TRIM(SUBSTITUTE(A1," ","")),4)

the -- stuff changes text numbers to number numbers.

And to excel:
'1234 (treated as text) isn't the same as 1234 (a real number).

 

[Heine]

If you want to match up using numbers--not text that looks like numbers, try
this:

=--RIGHT(TRIM(SUBSTITUTE(A1,CHAR(32),"")),4)
or
=--RIGHT(TRIM(SUBSTITUTE(A1," ","")),4)

the -- stuff changes text numbers to number numbers.

And to excel:
'1234 (treated as text) isn't the same as 1234 (a real number).








--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Thanks Dave - that worked great - and really easy.

Best regards
Heine