Excel format question

[Anonymous]

I have a column of SSNs entered like this

'333-22-444

and I want to convert those values from text to number format. Does anyone have any idea how to do this?

[urysohn]

a1=333-22-4444

b1=substitute(a1,"-","")+0

[Patience]

+value(left(A1,3)&mid(A1,5,2)&right(A1,3))

btw you are missing a digit, which would make the last statement Right(A1,4)

[Anonymous]

a1=333-22-4444

b1=substitute(a1,"-","")+0

Thanks. That was the missing piece that I needed.

:notworth: :tup:

[Dr T Non-Fan]

You plan to use arithmetic functions on SSN's? How intriguing...

[Anonymous]

You plan to use arithmetic functions on SSN's? How intriguing...

Actually I'm using them as a reference in a VLOOKUP function.

[Brad Gile]

a1=333-22-4444

b1=substitute(a1,"-","")+0

With my limited knowledge of the built in Excel functions, I might never have gotten this. Instead, I would have used
bi=SSNValue(a1)
where SSNValue is a user defined function:

Public Function SSNValue(ByVal X As String) As Double
'Transforms SSN xxx-yyy-zzzz to number xxxyyyzzzz
Dim S As String

S = Replace(X, "-", "")
SSNValue = Val(S)

End Function

This is, of course, essentially the same thing. This just illustrates that most of the built in Excel functions can be reproduced (sometimes improved!) with your own VBA functions! :D

Brad
"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

[Patience]

Be aware you will lose leading 0's in the numeric

[cubedbee]

This is, of course, essentially the same thing. This just illustrates that most of the built in Excel functions can be reproduced (sometimes improved!) with your own VBA functions! :D



Needlessly reproducing pre-existing functions seems like a waste though. Unless you're just trying to show off :roll2:

[Brad Gile]

You plan to use arithmetic functions on SSN's? How intriguing...

Actually I'm using them as a reference in a VLOOKUP function.

But why change the SSNs to numbers? Strings can be used as references, too. Moreover, you needn't sort the range by references as long as you put in the (optional) fourth parameter as FALSE (I think this is new to Excel 2000 and XP?).

Brad
"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

[Brad Gile]

This is, of course, essentially the same thing. This just illustrates that most of the built in Excel functions can be reproduced (sometimes improved!) with your own VBA functions! :D



Needlessly reproducing pre-existing functions seems like a waste though. Unless you're just trying to show off :roll2:

If I know the built in function OR can easily find it, I will use it. Otherwise, I roll my own to avoid wasting my time. As it happens, some of the built in functions, like NORMSINV, have minor flaws that can be fixed.

Another advantage to using VBA is that you can document exactly what you want with as much detail as you like. THAT is mighty useful 6 months from now, I assure you. :D

BTW, I have no need to "show off". :P

Brad
"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

[Emily]

Personally, I like to change all my SSN's to numbers for consistency. After all, the N does stand for Number. You can get your leading zeros and your hyphens by formatting, and if you have to enter one manually you don't have to type the hyphens.

p.s. If you write a user defined function to convert "123-45-6789" to a number you've got too much time on your hands.

[Dr T Non-Fan]

And make sure to have "0" as the first number in the data range.
Unless you KNOW that each number you plan to lookup is actually in the data range.
Normally, I just put them in order. Or I use SUMPRODUCT to eliminate "#REF#" values.

You could turn the data range's SSNs into strings, if that's easier (not so if the data comes from an uncontrollable source).

[Cho Da]

Moreover, you needn't sort the range by references as long as you put in the (optional) fourth parameter as FALSE (I think this is new to Excel 2000 and XP?).

In a long list, having the 4th parameter as FALSE has a huge performance hit. Since false assumes an unsorted list, on average it must look at half the entries before finding the correct one (and all of them if is missing). Having the 4th param be TRUE requires a sorted list, but if the list is long it is well worth it as the average search needs only look at log₂N entries.

If you search for a missing item you get the next one in the list. If you want the na, or a zero try this:=if(vlookup(a2,list,1,true)=a2,vlookup(a2,lis t,2,true),na())
When the list is long, the two lookups will still be faster than using vlookup(a2,list,2,false)

[Anonymous]

You plan to use arithmetic functions on SSN's? How intriguing...

Actually I'm using them as a reference in a VLOOKUP function.

But why change the SSNs to numbers? Strings can be used as references, too. Moreover, you needn't sort the range by references as long as you put in the (optional) fourth parameter as FALSE (I think this is new to Excel 2000 and XP?).

Brad
"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

I'm using a table with numeric SSN values.

[Brad Gile]

You plan to use arithmetic functions on SSN's? How intriguing...

Actually I'm using them as a reference in a VLOOKUP function.

But why change the SSNs to numbers? Strings can be used as references, too. Moreover, you needn't sort the range by references as long as you put in the (optional) fourth parameter as FALSE (I think this is new to Excel 2000 and XP?).

Brad
"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

I'm using a table with numeric SSN values.

That's as good a reason as any! :D

Brad
"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

[Brad Gile]

Moreover, you needn't sort the range by references as long as you put in the (optional) fourth parameter as FALSE (I think this is new to Excel 2000 and XP?).

In a long list, having the 4th parameter as FALSE has a huge performance hit. Since false assumes an unsorted list, on average it must look at half the entries before finding the correct one (and all of them if is missing). Having the 4th param be TRUE requires a sorted list, but if the list is long it is well worth it as the average search needs only look at log<sub>2</sub>N entries.

If you search for a missing item you get the next one in the list. If you want the na, or a zero try this:=if(vlookup(a2,list,1,true)=a2,vlookup(a2,lis t,2,true),na())
When the list is long, the two lookups will still be faster than using vlookup(a2,list,2,false)

Very true. Excellent point. But what if your list is one that is continually updated with additions/deletions. Then do you want to sort before the hunt or not? My answer="perhaps". In any case, I was not advocating use of the optional parameter as a panacea.


Brad

"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

[Brad Gile]

p.s. If you write a user defined function to convert "123-45-6789" to a number you've got too much time on your hands.

I use UDFs for a huge variety of things and have saved myself a lot of time and hassle for a number of years. Sometimes I could have used built ins, but mostly not (that I know of). Others would have done things differently. De gustibus non disputandum est. :D

Brad
"A generalisation is a modelling "technique" used to sacrifice
some accuracy for the benefit of model simplicity and scenario
comprehension. Over-generalisation is the state of losing so much
accuracy in the "simplification" that your model is no longer
suitable for its purpose."-Owen Buckingham

[Kenny]

I have a column of SSNs entered like this

'333-22-444

and I want to convert those values from text to number format. Does anyone have any idea how to do this?

Highlight the column, or cells if you have something below the SSNs. hit CTRL-H (replace) and replace - with nothing.

Unless, of course, you like having extra columns or writing VBA functions!

[Anonymous]

I have a column of SSNs entered like this

'333-22-444

and I want to convert those values from text to number format. Does anyone have any idea how to do this?

Highlight the column, or cells if you have something below the SSNs. hit CTRL-H (replace) and replace - with nothing.

Unless, of course, you like having extra columns or writing VBA functions!

Thanks. That's a little quicker! :notworth:

[Patience]

I have a column of SSNs entered like this

'333-22-444

and I want to convert those values from text to number format. Does anyone have any idea how to do this?

Highlight the column, or cells if you have something below the SSNs. hit CTRL-H (replace) and replace - with nothing.

Unless, of course, you like having extra columns or writing VBA functions!

I believe that will still leave him with text

[Anonymous]

I have a column of SSNs entered like this

'333-22-444

and I want to convert those values from text to number format. Does anyone have any idea how to do this?

Highlight the column, or cells if you have something below the SSNs. hit CTRL-H (replace) and replace - with nothing.

Unless, of course, you like having extra columns or writing VBA functions!

I believe that will still leave him with text

It does, and the original solution did as well. But it's in a text format that I can quickly format to numbers.

[urysohn]

I believe that will still leave him with text
Which can be changed using the Format-Cells command while the cells are still highlighted.