Excel 2013 - Is there a way to count the number of occurrences of a specific cha

Question

Excel 2013 - Is there a way to count the number of occurrences of a specific character in a cell?

I have copied data from a scanned and OCR'd file that includes a text string with numbers, and text separated by spaces. The number of spaces varies, however the data I want is in the middle. (I have used the LEFT and RIGHT functions to get the data on the ends).

I have been able to use a series of LEFT formulas to break the text apart into separate words - so far so good. Now I would like to reassemble just the central portion back into the basic text string. The problem is that the text strings vary from one word to 15 words.

Sample text string: "10 xyz123 text description of an item varies from one word to many supplier name ref qty"

(these represent table values of " Ref / Part Nr / Description / PN / Mfg / Ref / Qty")

The first portion of the string is consistent (the first three fields are always populated). If I could count the total number of spaces, I think I could reassemble the text strings.

Is there a way to count the number of occurrences of a specific character in a particular cell?

(In the above Sample, the number of spaces would be "10".)

Is there an easier way???

Explanation / Answer

You can count instances of a particular character like this which counts spaces.

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

If you copy a sample of these text strings and paste them into a reply then someone will work out the easiest method for you.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.