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

bekyleigh

New Member
Joined
Oct 16, 2019
Messages
12
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
ABCD
1Current Bolt on ValueFormula UsedResult from FormulaResult wanted
24GEE Data 2GBLEFT(E7,SEARCH("GB",E7)-1)4GEE Data 22GB
38GB Data and WiFiLEFT(E8,SEARCH("GB",E8)-1)88GB
44GEE Data 2GBLEFT(E8,SEARCH("GB",E8)-1)4GEE Data 22GB
516GB Data and WiFiLEFT(E8,SEARCH("GB",E8)-1)1616GB
Sheet1


Any help is much appreciated! I feel like I'm close but something isn't quiet right!....
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Welcome to the forum.

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

Excel Formula:
=TRIM(MID(" "&E7,SEARCH("GB"," "&E7)-2,4))
 
Last edited by a moderator:
Upvote 0
How about

Book1
EF
6Current Bolt on ValueResult wanted
74GEE Data 2GB2GB
88GB Data and WiFi8GB
94GEE Data 2GB2GB
1016GB Data and WiFi6GB
Sheet1
Cell Formulas
RangeFormula
F7:F10F7=TRIM(RIGHT(MID(" "&E7,SEARCH("GB",E7)-1,5),4))
 
Last edited:
Upvote 0
Solution
This worked brilliantly! I just had to adjust it slightly to (
Excel Formula:
=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! :)
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top