I'm trying to pull the middle portion of a string in a cell in my excel file using a single formule. An example of the input that I have can be found below:

Fruit: Apples
Color: Green

In this example, I'd like for the formula to only show me "Apples." The cell consistently cotntains "Fruit:" and "Color:" within it.

Thanks in advance for any guidance you're able to provide!

CodePudding user response：

The approach will depend on your version of Excel, as Excel O365 has some really powerful tools to make this easier. Assuming you don’t have O365, then her’e how I’d go about doing this.

I’m assuming that the value you want always starts in the 8th character in each cell, after “Fruit: “. So we don’t even have to search for “Fruit: “ or calculate its length. All we need to do is find “ Color:” and wherever that string starts is the end of you desired string. So if your string is in A2 for example, try this:

=MID(A2,8, FIND(" Color:",A2)-8)

CodePudding user response：

If you have TEXTSPLIT available, then assuming you have two rows and you want to extract the information after any of the tokens, then you can use:

=TEXTSPLIT(A1:A2, {"Fruit: ","Color: "},,1)

and here is the corresponding output.

You can also use TEXTAFTER in a similar way.

=TEXTAFTER(A1:A2, {"Fruit: ","Color: "})

If you don't have such functions available, then you can use the following:

=SUBSTITUTE(SUBSTITUTE(A1:A2, "Fruit: ",""), "Color: ","")

Please confirm if this is the expected input and output