How to extract data from one cell to another

Question

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.

score 2 · Answer 1 · answered Jun 29 '16 at 12:06

2

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:

simple

Cons:

work only with constant strings

answered Jun 29 '16 at 12:06

PipBoy2000

440
7
21

Use my formula if you want a variable-size string. – Ralph Jun 29 '16 at 12:10

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

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.

edited May 23 '17 at 10:28

Community

1
1

answered Jun 29 '16 at 12:10

Jim K

12,824
2
22
51

There is no mentioning of VBA. Instead the OP asked for a formula. With VBA it's easy and could actually be much shorter. – Ralph Jun 29 '16 at 12:15
@Ralph -- did you see the other question that is linked? – Jim K Jun 29 '16 at 12:16
Yes I did. So, you are a big fan of VBA. Great. Me too. Yet, the OP posted a new question without referencing this post. I guess there might be a reason to that. And indeed (as you can see in the post and his own answer) he was looking for a formula and not another VBA solution. – Ralph Jun 29 '16 at 12:29

How to extract data from one cell to another

2 Answers2

Linked