Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove characters from a string after a certain word - excel

Ive got a list of imported data that is formatted as the following in a excel / google spreadsheet. In column A i have the full data and in B im trying to strip out the data to the left of the word ON.

FULL DATA                      |      STRIPPED DATA


NNK GOV ON 31 AUG CDE          |      NNK GOV 
GIFFGAFF.COM ON 30 AUG CDE     |      GIFFGAFF.COM
TMRCO STORES  ON 12 AUG   CDE  |      TMRCO STORES

I was using the following forumal, but it falls down where there is non uniform white space as there is on the last entry.

=LEFT(a1, LEN(a1)-14)

Is there a more robust way i can write this, so it dosnt fall over when there is non uniform whitespace or additional wording after "CDE" ?

Ive created a shared google spreadsheet here as a scratch pad : https://docs.google.com/spreadsheets/d/1iieR_hAk8qJpy8W6Qq7ww9-640o-HZMFLNNjecps6m4/edit#gid=0

Im using google spreadsheets, although ive marked this question as excel also as ive found there to be many shared functions.


like image 709
sam Avatar asked Feb 02 '16 21:02

sam


People also ask

How do I extract characters after a word in Excel?

To get text following a specific character, you use a slightly different approach: get the position of the character with either SEARCH or FIND, subtract that number from the total string length returned by the LEN function, and extract that many characters from the end of the string.

How do I remove a string after the nth character in Excel?

Tip: If you want to remove string after nth character, you just need to type n + 1 into the Specify textbox.


2 Answers

This works:

=REGEXEXTRACT(A3,"(.*) ON .*")

I also added it on your sheet in the stripped column.

If you want to check if the condition exists, otherwise it might return an error:

=IF(ISTEXT(REGEXEXTRACT(A3,"(.*) ON .*")),REGEXEXTRACT(A3,"(.*) ON .*"), A3 ) 
like image 68
Aurielle Perlmann Avatar answered Nov 15 '22 06:11

Aurielle Perlmann


In excel:

=TRIM(LEFT(A1,FIND(" ON",A1)))

This will work in Google sheets also but there may be an easier way in google sheets that uses Regex.

enter image description here

like image 34
Scott Craner Avatar answered Nov 15 '22 05:11

Scott Craner