Excel - Compare two columns choose numeric value 2

[eggohead]

Hello,

I have two columns with similar data. In a third column I need to extract the numeric value of the two - whichever column has it. The problem is both columns will have text data in them and are not consistent.

Need to check both columns ignoring any text in both and output the numeric value (text removed in the third column. Ex: Row 2 would yield 9140 in the third column. Row 3 would yield 5991. Row 4 would yield "no Data"

Can this be accomplished in a formula or a simple macro? Keep in mind the column data is not consistent so some location data will be St 9140 or St - 8006

Store Location
mortega4 St 9140
pdunn3 5991
ggaliva ggaliva
5901 Store
9660 9660
5037 Store
pmcgrat 8685
8580 Store
7723 Store
afergus8 9698
7949 Store
mliceri st - 8006
jmendoz16 9063

Please let me know your suggestions. Thank your for your time.

 

[Fenrirshowl]

Hi

If I understand your data correctly (from what you have provided), there should be a 4 digit reference coming at the end of one of the two entries?

If this is right, the following might work (assuming your lists are in columns A & B):

IF(ISNUMBER(VALUE(RIGHT(B2,4))),VALUE(RIGHT(B2,4)),IF(ISNUMBER(VALUE(RIGHT(A2,4))),VALUE(RIGHT(A2,4)),"Error"))

The formula returns "Error" if neither entry ends in a 4 digit reference.

Fen

 

[SkipVought]

There is some logic that you are not stating.

You state...

1. I need to extract the numeric value of the two

2. check both columns ignoring any text in both and output the numeric value

3. Ex: Row 2 would yield 9140 in the third column

Then the row 2 example has numeric characters in both columns...
[tt]
mortega4 St 9140
[/tt]
According the your second criteia, would the 4 in the first column not be included in some way that you have not stated, or excluded in some way that you have not stated???

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[eggohead]

Hey Skip good point. In some rows there will be a logon id which can have a number or two. So there would need to be some logic to strip all the text and choose the greatest value from the two columns with the requirement that it has 3 to 4 numeric digits. Sounds kind of complicated. Im just trying to interpolate consistent data from two columns that are not consistent.

 

[eggohead]

Fen,

Your formula worked pretty good - thanks!

 

[SkipVought]

What is the business case for this task?

What is the logical relationship between Store and Location?

What makes the larger value significant?

Please answer ALL questions.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}