How do I Remove Hyphens from a FedEx number in Excel? 8

[scootswiss]

Our company is mail order and receives updates from our drop shippers on orders filled. This information is in an Excel spreadsheet. One of the fields has the FedEx number formatted like this 0300-1290-0006-4640-9750. To upload this information to our mainframe system the field can not contain the hyphens, just 03001290000646409750. How can I strip out the hyphens using Excel? Tried replace, trim and value ... no luck.

Thanks in advance for any help.

 

[Goska]

Try this, but it is going to be messy. It should work though, assuming that all FedEx numbers are 5 groups of 4 digits. It returns the number as a string.

Sub GetNum()
Dim Num1 As String
Dim Num2 As String
Dim Cut As Integer
Dim i As Integer

Num1 = Range("A1&quot
'GETS INPUT FROM CELL A1
Num2 = Left(Num1, 4)
'STORES THE FIRST 4 DIGITS OF THE CODE IN NUM2

For i = 1 To 4
Cut = 24 - (5 * i)
Num1 = Right(Num1, Cut)
'SETS NUM1=EVERYTHING TO THE RIGHT OF THE iTH HYPHEN
Num2 = Num2 & Left(Num1, 4)
'ADDS THE NEXT 4 DIGITS TO NUM2 AS A STRING
Next i

Range("A2&quot.NumberFormat = "@"
'SETS CELLS A2 AS TEXT, ELSE IT TURNS UP ROUNDED OFF AS AN INTEGER
Range("A2&quot = Num2
'PUTS THE CODE IN A2
End Sub

 

[zhendrickson]

Assuming 0300-1290-0006-4640-9750 is in cell A1, try:

=SUBSTITUTE(A1,"-","&quot

 

[Goska]

I like that one much better than mine.

 

[DaleWatson123321]

Hi scootswiss,

Your initial attempt to use "search/replace" did not work because the numbers must first be converted to LABELS.

Your search/replace option can be made to work IF you can convert the numbers to LABELS. This means having to insert a "LABEL PREFIX" which is the ' character (the one normally located to the immediate left of the Enter key on most keyboards).

You could use code to add the ' prefix, or you could possibly use search/replace IF you can be certain that the first four numbers will always be 0300 (and not included anywhere else in the same number). Then your search/replace would simply replace the 0300 with the added ' ...i.e. '0300.

If this is an option, then having first added the ' prefix, your search/replace for the HYPHENS will work.

Unfortunately, Excel does NOT have a LABEL format option. Lotus 123, by comparison, DOES have such an added format option. Hopefully, Microsoft is listening and will include this same LABEL format option ...which has existed elsewhere FOR SEVERAL YEARS.

Another example of Microsoft's obvious lack of concern, is its LONGSTANDING FAILURE to address the problem of Excel users being UNABLE to print two separate ranges on one page. I have complained directly to Microsoft for years, with NO results. I have also posted this problem in this forum, with a challenge to Excel experts to come up with a solution, and NO ONE has been able to do so.

It is time Microsoft started listening to these pleas for help in improving their products. Probably the approach they need to adopt is to FIRST fix these longstanding bugs, and THEN work on adding additional BELLS and WHISTLES.

Hope this has helped.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

While I was writing my reponse, I see that "zhendrickson" beat me to the draw, and provided AN EXCELLENT SOLUTION. Congatulations to him - I gave him a STAR.

...Dale Watson dwatson@bsi.gov.mb.ca

 

[Databaseguy]

Yes, that's about as simple and elegant as possible. I started with the VB route too. Kudos to zhendrickson

 

[scootswiss]

Thanks everyone for your suggestions. Problem solved.

scootswiss