Excel 2003 - Numbers vs text

[Craig Brandt]

This has me cofused to no end.

I have two tables each with account numbers. In one table they are shown
with a dash (123-456789), in the other, with nodash (123456789). Several of
the account numbers actually have an alpha character (X04456789 /
X04-456789). I have tried just about everything to get a good compare to no
avail.

Is there a surefire way to do a comparison? I can get the alpha-numeric
nember to match but none of the others.

Craig

 

[T. Valko]

How do you want to compare them?

123456789 = numeric number
123-456789 = text string

You can virtually "edit out" the dash but then you still have 2 different
data types. However, you can manipulate either to be the same data type.

A1 = 123456789
B1 = 123-456789

=A1&""=SUBSTITUTE(B1,"-","")
=A1=--SUBSTITUTE(B1,"-","")

The alphanumeric strings won't need any data type manipulation, just the
"edit out" of the dash.

A1 = X04456789
B1 = X04-456789

=A1=SUBSTITUTE(B1,"-","")

 

[Shane Devenshire]

Hi,

If you have a lot of data like this and you are willing to live without the
dashes, then try this

1. Select all the data, both tables and press Ctrl+H, enter a hyphen in the
Find what box and nothing in the Replace with box. Click Replace All
2. Select an empty cell and press Ctrl+C
3. Select all the data and choose Edit, Paste Special, Add.

All entries with just numbers should be text and all entries that contain
alpha characters will still be text.

If this helps, please click the Yes button

Cheers,
Shane Devenshire