Excel Text Formatting Question? 3

[specktur]

I have an Excel spreadsheet with text values in a column.

1602-552-113
B900198
1602-068-007
00152060
00307942
049-043-820-6
0493834908
049-387-130-0
049-387-150-0
AZPF-10-019LCP20KB-S0007

This is some sample data. I would like to remove ALL the hyphens. I can use the find/replace method but I encounter an error. If I use find/replace it removes the leading zero.
The problem lies in the data, the data in the field is inconsistent. These are part numbers that we use for parts we manufacture. Some of these parts are 10 characters and some are 21, some are letters and some are numbers only.
If there is a leading zero I need it to stay, and I cant figure out how to do so, I have tried formulas, format, etc…

Any help is greatly appreciated.

 

[AnotherHiggins]

The problem is that once the hyphens are removed, Excel is seeing the cell as a number, and that removes the leading zeros.

Format the column as text, then do Find and Replace.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

Excel makes assumptions about values that you enter. Keep in mind that there is a HUGE difference between NUMBERS and STRINGS of numeric digits -- HUGE.

What you WANT to have are STRINGS of NUMERIC DIGITS and NOT NUMBERS.

The STRING formatter is a [TIC] perceeding your numbers. For instance enter
[tt]
'00152060
[/tt]
and Excel interprets this as a STRING.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[KenWright]

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

and copy down.

then just copy / paste special values

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[specktur]

The column is formatted as text. That is what I originally tried. There are 6,419 cells in (one) column alone. There are about 4 columns I need to do this to. I have to feed this data back into our AS400 without the hyphens or (TICs). Excel might not be the best option, if you have any suggestions outside excel (access, or other db software let me know.) Thanks again.

 

[specktur]

Ken after that only the formula shows up.

 

[KenWright]

LOL - You need to ensure your column that the formula goes into is NOT formatted as text first. Then put in the formula and copy down.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Ahhhhhhhhh, Ken!

Such elegant simplicity! ==> *

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[specktur]

Dont I feel like a dummy..

 

[specktur]

Thanks for all the help. You get a star!!!

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

The only real dummies are the ones that don't ask for help for fear of looking like dummies. If I don't know and someone else might, then I'm asking asap.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

Sorry - that's what happens when you submit an answer without trying it first.

Glad Ken could sort it out.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.