=MID(A1,FIND("Serial number",A1)+15,5)

Solved

Posted on 2010-11-24

I have text strings from each of which I need to extract a specific character string. The character string I need to extract always has the same number of characters however the original text strings are not all the same length. An example text string is below. The segment - Serial number: - is always the same. The string I need to extract is the 5 digits following Serial number: There is always a ; following the 5 digit serial number which I do not wish to include.

Equipment QD5fj425; Serial number: 93222; Firmware: V 4.47 - 11/08 AV; QD5 Version: V 6.01

Any help is appreciated. Thank you.

Equipment QD5fj425; Serial number: 93222; Firmware: V 4.47 - 11/08 AV; QD5 Version: V 6.01

Any help is appreciated. Thank you.

5 Comments

Comment Utility

This works for your example, but does rely on there being colon space after serial number.

=MID(A1,FIND("Serial number",A1)+15,5)

=MID(A1,FIND("Serial number",A1)+15,5)

Comment Utility

Considering you have the original text string in A1, and the segment string ("Serial number") in B1, the formula you are looking for in C1 is =LEFT(RIGHT(A1,LEN(A1)-FIND(B1,A1)-LEN(B1)),FIND(";",RIGHT(A1,LEN(A1)-FIND(B1,A1)-LEN(B1)))-1)

A step by step process to reach this would be:

1. Write the string you need to parse in A1, and the string that identifies the segment in B1

A1 = "Equipment QD5fj425; Serial number: 93222; Firmware: V 4.47 - 11/08 AV; QD5 Version: V 6.01"

B1 = "Serial number:" (the idea is to identify this segment position and find yourself at the beginning of the number you need to extract)

2. Identify the position of the segment in D1

D1 = FIND(B1,A1)

3. Trim the original string removing the text from the beginning to the segment position, including the segment. Have that in E4.

E1 = RIGHT(A1,LEN(A1)-D1-LEN(B1)) - returns the right side of the string having a length equal with the total length of the string minus the position at which the segment begins minus the segment length. )

E1 result should be 93222; Firmware: V 4.47 - 11/08 AV; QD5 Version: V 6.01

4. In F1, use the previous returned result to find the delimiter and return the left side of the string until delimiter position

F1=LEFT(E1,FIND(";",E1)-1) (returns 93222)

To reach the result provided as an answer, if you need the whole formula in a single string, just recurrently replace the computed cells with the formula contained in them until you base your formula only on A1 and B1.

You can also change B1 with the actual value you are looking for to use a single formula and a hardcoded string, but that won't be so easy to maintain.

B1 = LEFT(RIGHT(A1,LEN(A1)-FIND("Serial number:",A1)-LEN("Serial number:")),FIND(";",RIGHT(A1,LEN(A1)-FIND("Serial number:",A1)-LEN("Serial number:")))-1)

A step by step process to reach this would be:

1. Write the string you need to parse in A1, and the string that identifies the segment in B1

A1 = "Equipment QD5fj425; Serial number: 93222; Firmware: V 4.47 - 11/08 AV; QD5 Version: V 6.01"

B1 = "Serial number:" (the idea is to identify this segment position and find yourself at the beginning of the number you need to extract)

2. Identify the position of the segment in D1

D1 = FIND(B1,A1)

3. Trim the original string removing the text from the beginning to the segment position, including the segment. Have that in E4.

E1 = RIGHT(A1,LEN(A1)-D1-LEN(B1

E1 result should be 93222; Firmware: V 4.47 - 11/08 AV; QD5 Version: V 6.01

4. In F1, use the previous returned result to find the delimiter and return the left side of the string until delimiter position

F1=LEFT(E1,FIND(";",E1)-1)

To reach the result provided as an answer, if you need the whole formula in a single string, just recurrently replace the computed cells with the formula contained in them until you base your formula only on A1 and B1.

You can also change B1 with the actual value you are looking for to use a single formula and a hardcoded string, but that won't be so easy to maintain.

B1 = LEFT(RIGHT(A1,LEN(A1)-FIND

Comment Utility

=LEFT(MID(A1,FIND("Serial number: ",A1)+LEN("Serial number:")+1,LEN(A1)),5)

for a fixed length of 5

or

=LEFT(MID(A1,FIND("Serial number: ",A1)+LEN("Serial number:")+1,LEN(A1)),FIND("|",SUBSTITUTE(A1,";","|",2))-(FIND("Serial number: ",A1)+LEN("Serial number:")+1))

for a variable length

for a fixed length of 5

or

=LEFT(MID(A1,FIND("Serial number: ",A1)+LEN("Serial number:")+1,LEN(A1)),FIND(

for a variable length

Comment Utility

dcruickshank,

The User Defined Function (UDF) below is in the attached file.

Usage: =sno(A1)

where A1 is the cell containing the original string.

The UDF will cope with Serial Numbers up to 6 digits in length.

Hope that helps.

Patrick

The User Defined Function (UDF) below is in the attached file.

Usage: =sno(A1)

where A1 is the cell containing the original string.

The UDF will cope with Serial Numbers up to 6 digits in length.

Hope that helps.

Patrick

```
Function sno(ByVal rng As Range) As Long
Dim str1() As String
str1 = Split(rng, ";", , vbTextCompare)
sno = --Trim(Right(str1(1), 6))
End Function
```

serial-no-extract-01.xls
Comment Utility

Thank you very much! Works perfectly

Title | # Comments | Views | Activity |
---|---|---|---|

Excel and Formulas | 8 | 30 | |

Vba to delete entire row | 6 | 25 | |

VBA Code Mixed Combining Two User Forms | 7 | 37 | |

Populate data based ona criteria | 6 | 5 |

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**8** Experts available now in Live!