Formula to find a word in middle of a cell

[Scottpet]

Hi
I’m hoping this isn’t wishful thinking.

I have a long list of records all as text, and I need to extract part of the cell into a separate field. Each cell has a weight in it in ‘kg’ and I need to get the weight into a separate cell. I’ve always used the formula below to find what I’ve needed but I don’t know how to make it so that it basically finds the kg and then goes back as far as a space and returns everything from the space to (and including) the kg.

=MID(E23,FIND("kg",E23)-2,6)

Examples of how the weight is displayed in the records and they are not all in the same place.
6kg
14kg
5.7kg
12.4kg
and so on
Thanks in advance for your help.

[AliGW]

You need to provide samples of the entire cell contents, please.

[AliGW]

Having said that, this might work:

=TRIM(RIGHT(SUBSTITUTE(MID(E23,FIND("kg",E23)-10,12)," ",REPT(" ",10)),15))

[Scottpet]

Hi thanks for the help

this is examples of how the information is held


food 11.4kg beef
dry 2.27kg food
large 18.25kg wet mix
simple 6kg feed
premium 11kg dry

[AliGW]

See post #3.

[Scottpet]

Hi Ali
Thanks where the weight is short it is also returning some of the letters prior to it

total food 11.4kg beef 11.4kg
Populardry 2.27kg food 2.27kg
large 18.25kg wet mix 18.25kg
Cheapsimple 6kg feed le 6kg
premium 11kg dry m 11kg

[Scottpet]

Hi Ali

Thank you so much the formula work perfectly.

[Phuocam]

Or try:

=LOOKUP(10^35,--MID(A1,FIND("kg",A1)-{1,2,3,4,5,6,7,8,9},{1,2,3,4,5,6,7,8,9}))

[AliGW]

Try changing it to this:

=TRIM(RIGHT(SUBSTITUTE(MID(E23,FIND("kg",E23)-10,12)," ",REPT(" ",10)),9))