Excel | How to split string text between two different characters?

Question

This is my first time posting here, so bear with me.

I have the following string of characters in Cell A1:

C.CompanyName_E.234500_Y.Boston_S.Massachusetts_S.800x600_

I want to parse CompanyName (Text Between C. and _) into B2, 234500 into C2 (The number 234500 between E. and _), Boston (Text between Y. and _) into D2, and so on...

What is the best way to split a string of text based on two different characters? in this case X. and _

Thanks

Answer 1

Put this in B2:

=MID(TRIM(MID(SUBSTITUTE($A2,"_",REPT(" ",999)),(COLUMN(A:A)-1)*999+1,999)),3,999)

And copy/drag over and down.

Answer 2

Thanks for the quick response. I was able to figure it out using this formula: Formula

This only works when the prefixes to the fields are all different (A. B. C. D. E. etc)

Answer 3

You should use wildcards like this, where "?" is the wildcard for characters

dim str_to_cut as String, aux_str as String
dim str_ls as Variant()
dim workbook as workbook
set workbook = activeworkbook
dim ws as worksheets
set ws = workbook.sheets(1)

str_to_cut = C.CompanyName_E.234500_Y.Boston_S.Massachusetts_S.800x600_
str_ls = Split(str_to_cut, "_")

for x = 0 to Ubound(str_ls)-Lbound(str_ls)  #size the array
    aux_str = Split(str_ls(x), "=?.")(1)    #maybe you don't need the =
    ws.cells(1, x+2) = aux_str              #need to get the right column 
next x

you may need to adjust your logic on where to place the results and maybe some sintax, cheers