Help with formula for isolating text in Cell

[EQUILIBRIUM89]

Good afternoon everyone,

I am currently working on a project and am stumped on creating a formula for isolating a block of text within a cell. For example,

HEX,1YRBL,VTIS,V632,WETEND I only need V632. How would you isolate this? Note: the V632 is a part number and will change as there are 1000 part numbers. So I would need a formula that deals with positioning? Any help would be great. Thanks.

<tbody>
</tbody>

 

[JoeMo]

Will the part number always be between the last two commas in the cell?

 

[EQUILIBRIUM89]

No, unfortunately.

 

[Sektor]

UDF for your help.

Function GetNumber(cell As Range) As String
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    re.IgnoreCase = True
    re.Pattern = "\b\w\d+\b"
    GetNumber = re.Execute(cell.Value)(0)
    Set re = Nothing
End Function

 

[JoeMo]

No, unfortunately.

Then how exactly can it be distinguished from the rest of the text in a cell?

 

[EQUILIBRIUM89]

There are commas that seperate the other parts.

 

[Jonmo1]

There are commas that seperate the other parts.

There are commas between all parts..

How can we know 'which' commas designate the part you're looking for?

 

[Sektor]

Did you try UDF in 4-th message?

 

[EQUILIBRIUM89]

Sektor, I am trying to figure out how to input a UDF into code, im only familiar with recording macros.

 

[Sektor]

It's all simple.
1. Press Alt + F11.
2. Execute: Insert -> Module.
3. Paste UDF into code window.
4. Go to your worksheet and start typing (assuming the text is in cell A1): =GetNumber(A1)