Help with Mid-Function and variable lengths.

[spencerwegener]

Hi All,

Looking to see if it is possible to use a mid/find function to find the string starting point and then pull the next characters up to a space, or multiple spaces. Essentially I'm trying to parse data pulled from an email, however the email is not always structured the same, yet I do have labels that will always appear before the words I want to pull (ex. Name: ....., Email: .... ). I copied below the formula I have been using when I have 2 known variables, however if I can figure out how to search variable 1, and then pull a word up to a space next to it that would fantastic.

=MID(indirect("a"&row()),SEARCH("Variable1~:",indirect("a"&row()))+7,SEARCH("Variable2",indirect("a"&row()))-SEARCH("Variable1~:",indirect("a"&row()))-8)

Thank you so much,

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[mehmetcik]

This is a start

=MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1)))

It returns the position of the first occurrence of one of your search strings

This finds the position of the first space after that point

=FIND(" ",A1,MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1))))


Put those togeather

=MID(A1,MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1))),FIND(" ",A1,MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1))))-MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1))))

Returns your text proceeded by your search string

So you now need to get rid of your search string

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1))),FIND(" ",A1,MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1))))-MIN(IF(ISERROR(FIND("Name",A1)),999,FIND("Name",A1)),IF(ISERROR(FIND("Surname",A1)),999,FIND("Surname",A1)),IF(ISERROR(FIND("Email",A1)),999,FIND("Email",A1)))),"Name",""),"Surname",""),"Email","")

Messy ha?

I would use a userdefined function instead.