Extracting text from a cell when the location is different each time.

[bekyleigh]

Hi,

I need some help with an excel spreadsheet I'm building.

I've got a database which shows how much data each employee within the company has on their company mobile phone, but the layout provided from EE is awful, sometimes it might say "8GB Data & Wifi" other times it might say "4gEE Data 2GB". I want to create a formula which can be used to extract how many GB of data bolt on each employee has, and exclude everything else from the text,
Ideally I need a formula which will only pull the first 3 characters before "GB". (I think it needs to be 3 characters as some employees will be more than 10GB of data)

I've already tried using =LEFT =RIGHT but because the position changes constantly these aren't suitable. I attempted using =LEFT combined with =SEARCH but im still not getting the answer I want (See table below)

Book1
	A	B	C	D
1	Current Bolt on Value	Formula Used	Result from Formula	Result wanted
2	4GEE Data 2GB	LEFT(E7,SEARCH("GB",E7)-1)	4GEE Data 2	2GB
3	8GB Data and WiFi	LEFT(E8,SEARCH("GB",E8)-1)	8	8GB
4	4GEE Data 2GB	LEFT(E8,SEARCH("GB",E8)-1)	4GEE Data 2	2GB
5	16GB Data and WiFi	LEFT(E8,SEARCH("GB",E8)-1)	16	16GB
Sheet1

Any help is much appreciated! I feel like I'm close but something isn't quiet right!....

 

[RoryA]

Welcome to the forum.

Assuming you don't have anyone using 100GB or more, something like this should work:

=TRIM(MID(" "&E7,SEARCH("GB"," "&E7)-2,4))

 

[Fluff]

How about

Book1
	E	F
6	Current Bolt on Value	Result wanted
7	4GEE Data 2GB	2GB
8	8GB Data and WiFi	8GB
9	4GEE Data 2GB	2GB
10	16GB Data and WiFi	6GB
Sheet1

Cell Formulas
Range	Formula
F7:F10	F7	=TRIM(RIGHT(MID(" "&E7,SEARCH("GB",E7)-1,5),4))

 

[bekyleigh]

This worked brilliantly! I just had to adjust it slightly to (

=TRIM(RIGHT(MID("  "&E7,SEARCH("GB",E7)-1,5),5))

as I have some employees who have double-digit data allowances.

Thank you for your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

The formula that I posted & the simpler one that Rory posted both work, but the board strips out extra spaces.
so there should be two spaces between the " " not just one