CODEWITHSUNDEEP
excelexcel-formula
Vibhav MS
Vibhav MS

Reputation: 33

Split a string between two specific characters in Excel

I have a spreadsheet with more than 2000 rows and each row appears as below. I've pasted 2 rows :

ROW 1 CA WAAE JOB: "abc_def__ghijkl_mnop_qrst_c" JOBSET: "abc_def__ghijkl_mnop_qrst_b"   
ROW 2 CA WAAE JOB: "mon_bus_xyz_ff_kkkkk_abcdef_uvwx_b" JOBSET:"mon_bus_xyz_ff_kkkkk_abcdef_uvwx_b"

I want to copy the data present between the first set of "" i.e abc_def__ghijkl_mnop_qrst_c and mon_bus_xyz_ff_kkkkk_abcdef_uvwx_b and pasted them into another column. Would it be possible to do this?

Upvotes: 2

Views: 12297

Answers (2)

zx8754
zx8754

Reputation: 56179

Assuming your cell is A1, following formula will take 2 strings between (") and put them into one cell separated with "-".

=LEFT(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("""",A1)),""" JOBSET: """," - "),LEN(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("""",A1)),""" JOBSET: """," - "))-1)

Explanation:

=RIGHT(A1,LEN(A1)-FIND("""",A1)) - Remove all characters from left until first (") character. =SUBSTITUTE(A1,""" JOBSET: """," - ") - Substitute "JOBSET:" with a dash charachter "-". =LEFT(A1,LEN(A1)-1) - Drop the last (") character.

Nesting all into one formula is given above. Output:

abc_def__ghijkl_mnop_qrst_c - abc_def__ghijkl_mnop_qrst_b

Upvotes: 1

Abe Gold
Abe Gold

Reputation: 2357

Assuming the entry is in A1 then use:

=LEFT(MID(A1,FIND("""",A1,1)+1,9999),FIND("""",MID(A1,FIND("""",A1,1)+1,9999))-1)

Upvotes: 3

Related Questions