Loop through a string and find the character/string need

**dluhut** · 07-15-2014, 10:23 AM

Hello excel experts,

May I know what's the formula to search for a particular string?

i.e. I have QUOT20140711-9527-5212a priced on Monday. SOMETIMES I do have a space in between '-'.
i.e. I have QUOT20140711-9527- 5212a priced on Monday. Notice the extra space after the '-' and before 5212a?

My question is then, how do I get 5212a in this case?

I know for sure that it'll be QUOT[YYYYMMDD]-(_)####(_)-(_)@@@@@@(_)priced on
where (_) is a space depending on user input (some uses a space in between hyphens '-'
@@@@@@ could be any "PO" number

**Speshul** · 07-15-2014, 10:25 AM

Is it always the final 5 characters you are looking for?

Edit: actually, and I don't know your application or how many users there are, but would it be feasible to apply Data Validation to allow only a certain number of characters (assuming a space-free string is always the same length), to discourage non-data-critical entries such as spaces in the first please? This would force data integrity and allow for simpler data management.

**dluhut** · 07-15-2014, 10:29 AM

No...not necessary. that's why I put @@@@@ as it could be more or less than 5.

What is known for sure is that it'll be after a hyphen (e.g. 9527-5212a priced), or hyphen and space (e.g. 9527- 5212a priced) and then @@@@@ and then a space follow with the word 'priced'

**Speshul** · 07-15-2014, 10:34 AM

=SUBSTITUTE(A1," ","")

Will remove the spaces from A1. So use this formula anywhere you use an "A1" in the formula you currently use to extract the hyphen separated values.

**dluhut** · 07-15-2014, 10:42 AM

I guess I need to include another statement.

Currently I have the following formula in place that would give me ranking. However, because there's a space after a hyphen sometimes, it threw my ranking. Therefore, I'm now trying to find a way how to get the string and do a comparison. This way, doesn't matter how the user input is going to be on the string, it'll only compared the @@@@@.

With the substitute, I can't seems to implement it in my current formula.

Please Login or Register  to view this content.

**Speshul** · 07-15-2014, 10:55 AM

Try

=COUNTIFS($A:$A, "=QUOT*",$A:$A, "<="&SUBSTITUTE(A8," ",""))

I got 3 on each row when I posted the following in column A, with the previous formula I was getting 1, 2, 3. Is this what you need?
QUOT20 140 711- 9527- 521 2a
QUOT20140711-9527- 5212a
QUOT20140711-9527-5212a