Extracting a "non numeric" character from with a numeric value

[kevinparry1969]

I'm looking for a formula that can take out a "non numeric" character from within a number.

For example:

Values in column A are
123456-1
123456-2
123456-3
123456-4
123456-5
123456-6
123456-7
123456-8
123456-9
123456-10

I want to covert them so that they show in column B without the "-" character
1234561
1234562
1234563
1234564
1234565
1234566
1234567
1234568
1234569
12345610

Any ideas?

Thanks in advance.

 

[Marcelo Branco]

Try
=SUBSTITUTE(A1,"-","")+0

+0 converts the string to a number.

Hope this helps

M.

 

[broncosrul]

I generally just do this with FIND-REPLACE (ctrl-H). Find - and then leave the replace field blank.

 

[Rick Rothstein]

If the numbers all follow the pattern you showed in your example, then this formula will also work...

=REPLACE(A1,7,1,"")

 

[Peter_SSs]

Yet another option: If using Excel 2013 or later you can also use Excel's built-in Flash Fill feature by manually entering just the first couple of values to show the pattern you want & Excel can automatically fill the rest for you.

 

[kevinparry1969]

HI all,

Thankyou for your suggestions so far

The numbers I gave in my original post were just examples but they follow the same format to what I will be using.
The first 6 digits are always followed by a "-" then more more digits follow. The formula needs to extract the "-" just leaving all the digits with no gap so they become a unique number on their own. The number of digits before the "-" will be 6 but at some point will increase to up to 8 although it will be a long time before they go that far.

However the number of digits after the "-" could be anything from a single digit all the way up to 4 digits long so the formula needs to be able to take this into account.

123456-10 needs to be converted to 12345610
1234567-11 needs to be converted to 123456711
12345678-999 needs to be converted to 12345678999 etc

FIND REPLACE function can be used but would be too manual. Needs to done via a formula so that it's done automatically behind the scenes.

I'm aware of Excels Flash fill function and this was how I thought I'd go with this but with all the variations in what numbers I could be face with its not a option.

=REPLACE(A1,7,1,"") wouldn't cover all the variations as it relies on the "-" being the 7th character. That works at the moment but as soon as the first number move to 7 and 8 digits before the "-" I'd have to alter the formula and for quite some time I'd have a mixture of all those variations.

=SUBSTITUTE(A1,"-","")+0 round the issue so I'm going to have a play with that when I get into the office on Monday.

Once again thankyou for all all your suggestions.

Will update on Monday

 

[Peter_SSs]

I'm aware of Excels Flash fill function and this was how I thought I'd go with this but with all the variations in what numbers I could be face with its not a option.

With the "-" in a mixture of the 7th, 8th or 9th position and followed by from 1 to 4 further digits, Flash Fill still worked for me. However, if you will be continually adding to your data, it might be simplest to go with a formula that automatically fills down your table as you add the additional data.

 

[kevinparry1969]

Hi Peter,
The data will be continually added too as the spreadsheet is reading barcode data from around 50 scanners dotted around a large manufacturing facility. Quite a large volume of data coming in throughout the working day so a formula solution is the way I'll be going. Once I've done a few things I'll update everyone on here so hopefully anyone else has any similar issues it might help them.