Given a spreadsheet cell containing a string that consists of a hyphenated series of character segments, I need to extract the fourth segment.
For example, consider column A containing data strings like XX-XXX-X-G10-XX-XXX, where X denotes any character. What formula would I need to place in column B to get G10 as a result?
A B
1 XX-XXX-X-G10-XX-XXX G10
I'm looking for a formula that could work in in Libre Office Calc, Open Office Calc, MS Excel, or Google Sheets.
It was very simple... MID was the right function:
=MID(A1;10;3)
where:
- A1 is my data
- 10 start from 10 character - in my case G
- 3 range of characters count from 10 (10, 11, 12)
Pros:
Cons:
- work only with constant strings
One way is to use the following general-purpose macro (tested in LibreOffice):
Function SplitAndExtract(findIn As String, delims As String, _
Optional segment_param As Integer)
' findIn - string or cell to search in
' delims - the delimiters to split the string up by
' segment - which segment number to grab
If IsMissing (segment_param) Then
segment = 0
Else
segment = segment_param
End If
splits = Split(findIn, delims)
If UBound(splits) < segment - 1 Then
SplitAndExtract = "No result for that segment"
MsgBox "No result for that segment"
Exit Function
Else
SplitAndExtract = splits(segment)
End If
End Function
Then set the cell formula to:
=SPLITANDEXTRACT(A1, "-", 3)
This is similar to my answer to your other question: https://stackoverflow.com/a/38085634/5100564. Thanks to @Ralph for suggesting the Split method.
