CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user3737374
user3737374

Reputation: 21

Extracting a string from a cell

I have some text in a cell in google sheets, example - February1—SG4601382 (PS xxxxx 12345.666 and 12345.667)

I'm trying to extract just the SG4601382 part into another cell, the S character starting position varies in each cell as sometime the date may be February1 sometimes February12.

Do any of you good people know how I can do this?

Upvotes: 0

Views: 96

Answers (4)

marikamitsos
marikamitsos

Reputation: 10573

The shortest one would be

=REGEXEXTRACT(B2,"—(\w+)")

enter image description here

Or as an array

=INDEX(IFERROR(
     REGEXEXTRACT(B2:B,"—(\w+)")))

(Do adjust ranges and locale to your needs)

EDIT (following JvdV's comment)

As an alternative so as to "free all cells below", try

=QUERY(INDEX(REGEXEXTRACT(B2:B,"—(\w+)")), 
          "where Col1<>'#N/A' ")

Upvotes: 3

JvdV
JvdV

Reputation: 75990

If your data is generic enough to look for the "—", try:

=REGEXEXTRACT(A1,"—([A-Z0\d]+)")

Or a little less specific:

=REGEXEXTRACT(A1,"—(\S+)")

enter image description here

Edit; As an array:

=INDEX(IF(A1:A="","",(REGEXEXTRACT(A1:A,"—(\S+)"))),)

Upvotes: 1

JPV
JPV

Reputation: 27302

Alternatively, you should be able to use regexextract.

=regexextract(A2, "—(.*?)\s")

enter image description here

This can be turned into an arrayformula easily.

=Arrayformula(if(len(A2:A), regexextract(A2:A, "—(.*?)\s"),))

enter image description here

Upvotes: 2

Deven Ramani
Deven Ramani

Reputation: 781

You may use the SEARCH function to find the position of "S"

for example if "February1—SG4601382 (PS xxxxx 12345.666 and 12345.667)" is your text in A1 cell then

=SEARCH("S",A1,0)

which give you the position of "S" as 11

now if SG4601382 is the string with length 10 every time then you can extract it with MID formula like below

=MID(A1,SEARCH("S",A1,0),10)

gives SG4601382

Upvotes: 1

Related Questions