How to extract parsed data from once cell to anoth

Question

Given a spreadsheet cell containing a string that consists of a hyphenated series of character segments, I need to extract the last segment.

For example, consider column A containing data strings like XX-XXX-X-XX-XX-G10, 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-XX-XX-G10      G10

I'm looking for a formula that could work in in Libre Office Calc, Open Office Calc, MS Excel, or Google Sheets.

Answer 1

In LibreOffice Calc and OpenOffice Calc, you can use a regular expression to determine the position of the text after the last - character:

=SEARCH("-[:alnum:]+$";A1)

will return 15 if A1 contains XX-XXX-X-XX-XX-G10.

Now, you can use this value to get the text "behind" that position, using the RIGHT() function:

=RIGHT(A1;LEN(A1)-SEARCH("-[:alnum:]+$";A1))

Split up on multiple lines:

=RIGHT(                ' return text beginning from the right...
    A1;                ' of cell A1 ...
    LEN(A1)            ' start at lenght(A1) = 18
    -                  ' minus ...
    SEARCH(            ' position ...
        "-[:alnum:]+$" ' of last "-" ...
        ;A1            ' in cell A1 = 15 ==> last three characters
    )
)

Answer 2

I found simply solution:

=RIGHT(A1;3)

that gives me G10 as the result too! It works because COL A always have 3 chars at the end!

Answer 3

Another possibility in LO Calc is to use the general purpose regular expression macro shown here: https://superuser.com/a/1072196/541756. Then the cell formula would be similar to JPV's answer:

=REFIND(A1,"([^-]+$)")

Answer 4

It appears that you want the characters that appear at the end of a string, to the right of the last instance of a hyphen character, "-".

This formula, adapted from here, works in Excel, *Calc & Google Sheets:

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))

Explanation:

• SUBSTITUTE(A1,"-",new_string) will find each hyphen ("-") in the original string from cell A1 and replace it with a new_string.

• REPT(" ",LEN(A1)) is a string of repeated space characters (" "), the same length as the original string in cell A1.

• TRIM(RIGHT(string,count)) will get the right-most count characters, and trim off leading and trailing spaces. Since the string was previously padded out by replacing hyphens with spaces, and count is the same LEN(A1) used for that padding, the last count characters consists of a bunch of spaces followed by whatever followed the last hyphen!

In Google Sheets, an alternative approach is to use the SPLIT function to break the value from column A into an array, then select the last element. (Excel-VBA has a split() function, so you could make this work in Excel by writing VBA code to provide it as a custom function.)

=INDEX(SPLIT(A1,"-"),0,COUNTA(SPLIT(A1,"-")))

Answer 5

If you are using google sheets, regexextract would be possible too:

=REGEXEXTRACT(A1, "[^-]+$")