Remove dashes when between letter and number 3

[rcrecelius]

I have a problem that I face on a somewhat regular basis.
I have part numbers which may have a combination of letters, dashes, and numbers. I have to go thru and manually delete the dashes from some part numbers but not all of them.
This is the desired "format"...
If the dash is between a letter and a number - delete it.
If the dash is between 2 numbers or 2 letters - keep it.

"Good examples" - keep the dash
A10-1
204-166
735-10
2035E-S

"Bad examples" - delete the dash
A-10
2035-ES

Im dealing with files sent from our vendors so the format varies...sometimes all are formatted as text and other times only the ones that contain letters are formatted as text.

Is there an easy solution to this conditional dash delete?

 

[rcrecelius]

I suppose I should have noted that I am using Excel 2002.

 

[KenWright]

With your data in Col A starting A1, in B1 put the following and copy down:-

=IF((ISNUMBER(--MID(A1,FIND("-",A1)-1,1))*ISNUMBER(--MID(A1,FIND("-",A1)+1,1))+NOT(ISNUMBER(--MID(A1,FIND("-",A1)-1,1)))*NOT(ISNUMBER(--MID(A1,FIND("-",A1)+1,1)))),A1,SUBSTITUTE(A1,"-","",1))

When done, just copy and paste special as values

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

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

----------------------------------------------------------------------------

 

[Dreamboat]

Why not just find (hyphens) and replace with (nothing)??

Anne Troy

http://www.vbaexpress.com/forum

 

[KenWright]

LOL - Methinks you didn't read the whole post Anne

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

----------------------------------------------------------------------------

 

[Dreamboat]

RTFQ?? Me??
Hee hee.
Methinks you're right.
As per usual.



Anne Troy

http://www.vbaexpress.com/forum

 

[rcrecelius]

KenWright,
This is a blessing...I copied it into a sheet and it worked flawlessly! Thank you.

Now, can you explain how this formula works? Formulas with that many "is" and "is nots" are confusing to me.

 

[KenWright]

Oh boy, the fun part

=IF((ISNUMBER(--MID(A1,FIND("-",A1)-1,1))*ISNUMBER(--MID(A1,FIND("-",A1)+1,1))+NOT(ISNUMBER(--MID(A1,FIND("-",A1)-1,1)))*NOT(ISNUMBER(--MID(A1,FIND("-",A1)+1,1)))),A1,SUBSTITUTE(A1,"-","",1))


OK lets break it down:-

This thing really breaks into two main pieces of formula, and is just a great big IF statement:-

=IF(Condition TRUE, This, That) ==>

=IF(Condition TRUE,A1,SUBSTITUTE(A1,"-","",1))

Condition TRUE evaluation =

(ISNUMBER(--MID(A1,FIND("-",A1)-1,1))*ISNUMBER(--MID(A1,FIND("-",A1)+1,1))+NOT(ISNUMBER(--MID(A1,FIND("-",A1)-1,1)))*NOT(ISNUMBER(--MID(A1,FIND("-",A1)+1,1))))

Main gist of that ISNUMBER stuff is to see if we can get a 1 (Equates to TRUE in Excel) in the argument for the IF function. This is done by interrogating the characters eithers ide of the '-' to see if they match in context, eg both numeric or both text. If they do then the formula returns a 1, and if they don't then it's a 0.


First thing to do no matter what is to find where the '-' is in the string, hence the FIND("-",A1), and this will return a number such as 2,3,4,5,6,7,8 etc depending on the position within the string.

Next we need to take a look at the character either side of that '-', so hence the two MID statements:-

MID(A1,FIND("-",A1)-1,1) and
MID(A1,FIND("-",A1)+1,1)

Note the -1 and the +1. within the MID function these point you to the character before and after the '-'. The MID function without the +1 or -1 eg MID(A1,FIND("-",A1),1) effectively says take a look at the string in A1 and give me the character at the position specified by the result of the FIND statement. The +1 and -1 then add or subtract a 1 from the result of that FIND function and take you either side of that character.

Now, the result of a MID function is a always text, so we need to try and coerce the result to numeric if we can, hence the -- before each of the statements. This means that the first evaluation of each side of the '-' to see if it is numeric will give you either

1*1 = 1
1*0 = 0
0*1 = 0
0*0 = 0

Only the first combination can possibly result in a 1. We then do the opposite because it may be that neither of them are numeric, so hence the NOT(ISNUMBER bit, and once again we end up with the following possible combinations:-

1*1 = 1
1*0 = 0
0*1 = 0
0*0 = 0

The formula then procedes to apply both the above tests to the data and then add the results'

Now, it can never be the case that both are numeric and both are text, so you can never get a 2, ie if the data are both numeric then the first part of the formula gives you 1*1 whereas the second part gives you 0*0, and 1*1 + 0*0 gives you 1. Opposite if both were text ie if the data are both text then the first part of the formula gives you 0*0 whereas the second part gives you 1*1, and 0*0 + 1*1 gives you 1.

This value of 1 or 0 then gets fed through to the argument required for the IF function and then based on it being 1 or 0, ie TRUE or FALSE it then decides to give you one of the options eg with or without dashes.

Bit rambly but hopefully it gives you the gist

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



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

----------------------------------------------------------------------------

 

[rcrecelius]

You lost me on the -- before the MID function.

 

[KenWright]

The -- bit works to coerce data that looks numeric to actually be numeric. Just the same as if you formatted some cells as text and then put in some numbers, the numbers themselves would actually be text but you can still perform calculations on them, eg:-

Cell A1:A2 - formatted as text and 5 and 6 entered respectively - These are text and if you just tried =SUM(A1:A2) in another cell you would get 0 because it sees the data as text. If however, you try =A1*1+A2*1 you will indeed get 11. You could also use =SUM(A1:A2+0) array entered, which will in effect add 0 to each number before summing the array elements.

The fact that you have done something to these numbers mathematically means that Excel now realises that you want the data considered as numeric, and so putting -- in front of a text number converts it to negative and then back to positive giving you what you started with but in numeric format.

That way ISNUMBER(--MID(A1,FIND("-",A1)+1,1) evaluates as follows (Lets assume the string in A1 is 'ABCD2-4S' so that the '-' is in position 6).

ISNUMBER(--MID(A1,FIND("-",A1)+1,1)) ==>
ISNUMBER(--MID(A1,6+1,1)) ==>
ISNUMBER(--MID(A1,7,1)) ==>
The MID(A1,7,1) equals a text 4 ==>
ISNUMBER(--4) ==>
The -- converts text 4 to numeric 4 ==>
ISNUMBER(4) = TRUE

so for this instance you get a 1. The other part of the formula with the -1 bit in it does the same evaluation on the 2 and you get a 1 also. The ISNUMBER...*ISNUMBER.... bit gives you 1*1 = 1, and this will be added to the results of the NOT(ISNUMBER parts of the formula, which by default have to be 0 because we know the 2 and 4 are numbers. This gives you 1+0 = 1 and the 1 is what is fed through as the argument for the IF function.

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



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

----------------------------------------------------------------------------

 

[Dreamboat]

I don't have time to read it all, but I'm SURE it deserves a STAR!!

Anne Troy

http://www.vbaexpress.com/forum

 

[rcrecelius]

Ken,
Thanks for the explaination...this is valuable information for those of us who are just getting our feet wet in working with formulas.
Thanks again, I cant begin to tell you how much time this formula will save me!

 

[KenWright]

Anne - kisses and hugs

rr - LOL, If you get your head around that then you will already be using functions that the majority of users never even get near, so if this is your first foray into it then keep an eye on this forum and just watch some of the questions and see how they are answered.

Just knowing what it is capable of is the main part of being able to use Excel. Once you knwo what you want to achieve its just a case of finding how, but unless you know it can you don't even know to try and make it.

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

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

----------------------------------------------------------------------------

 

[rcrecelius]

I got thrown into the IT world and had to learn a lot by trial and error...I found out how to use lookups and vlookups by continuous experimentation and I have been able to use them successfully. I have learned a lot from this site and occassionally I can contribute something worthwhile as well.
All these formulas really seem to be simple logic but when you have to combine several things to get that one "true or false" it can really be mindboggling!

 

[KenWright]

Personally, I think if you can get your head round two things in Excel:-

1) SUMPRODUCT function
2) Pivot Tables

you will be well set to tackle a large number of Excel problems that can turn up, and will be capable of doing analysis that others wouldn't believe possible in literally minutes.

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

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

----------------------------------------------------------------------------