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.
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.
Tip: If you want to remove string after nth character, you just need to type n + 1 into the Specify textbox.
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 )
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With