Vlookup not recognising all numbers with alpha numeric mix

[Tanianiania]

Hello

We get the phone bill in a csv format which I am trying to get a vlookup to reference.

I have in column A on the spreadsheet how they want to view the numbers on the report.

I have a column B which I am hiding which formats the number to match how the CSV file comes up to do the lookup.

A small sample of the data in columns A and B below...

[TABLE="width: 325"]
<tbody>[TR]
[TD]A
N7759484R[/TD]
[TD]B
N7759484R[/TD]
[/TR]
[TR]
[TD]N7759484R[/TD]
[TD]N7759484R[/TD]
[/TR]
[TR]
[TD]N7764680R[/TD]
[TD]N7764680R[/TD]
[/TR]
[TR]
[TD]Y00000060736N[/TD]
[TD]Y00000060736N[/TD]
[/TR]
[TR]
[TD](07) 3030 5830[/TD]
[TD]730305830[/TD]
[/TR]
[TR]
[TD](07) 3205 7442[/TD]
[TD]732057442[/TD]
[/TR]
[TR]
[TD](07) 3490 2601[/TD]
[TD]734902601[/TD]
[/TR]
[TR]
[TD](07) 3490 2607[/TD]
[TD]734902607[/TD]
[/TR]
[TR]
[TD](07) 3490 2608[/TD]
[TD]734902608[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

The formula I put in B is

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4," ",""),"(0",""),")","")

This gets rid of the spaces, the brackets and the zero at the front. Column B looks exactly like the csv file, however the Vlookup on Column B only recognises the first 4 rows (alpha numeric) but it doesn't seem to recognise the numbers in the rest and returns an #N/A.

As the alpha numeric never has spaces I had the idea of the following IF formula -

=IF(ISNUMBER(SEARCH("N",$B4)),$B4,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4," ",""),"(0",""),")","")+0)

Where it only does the substitue on cells with a " " and +0 seems to turn it into a value. This concept works, however, the alpha part could be a number of letters not just "N".

Is there some kind of way you can do an IF contains an alpha figure, or, someone I can get the vlookup to recognise all figures.

Hope this makes sense, thanks

Tania

 

[Vidar]

Hi Tania
Would this work?
=IF(AND(CODE(LEFT($A1,1))>65,CODE(LEFT($A1,1))<90),$A1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," ",""),"(0",""),")","")+0)
Assuming the letter is always in uppercase.

Vidar

 

[Andrew Poulsom]

How about?

=IF(ISERROR(SEARCH(" ",$B4)),$B4,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4," ",""),"(0",""),")","")+0)

 

[Tanianiania]

Ah yes thankyou both work, the second is completely obvious to me now that I see it!

Thanks