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

bekyleigh

New Member
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)

Current Bolt on ValueFormula UsedResult from FormulaResult wanted
4GEE Data 2GB

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
=LEFT(E7,SEARCH("GB",E7)-1)<strike></strike>
<strike></strike>
4GEE Data 2

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
2GB
8GB Data and WiFi

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
LEFT(E8,SEARCH("GB",E8)-1)
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
8

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
8GB
4GEE Data 2GB

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
LEFT(E8,SEARCH("GB",E8)-1)
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
4GEE Data 2
<strike></strike>
2GB
16GB Data and WiFi

<colgroup><col></colgroup><tbody>
</tbody>

<strike></strike>
LEFT(E8,SEARCH("GB",E8)-1)
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>

1616GB

<tbody>
</tbody>

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

RoryA

MrExcel MVP, Moderator
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

MrExcel MVP, Moderator
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Current Bolt on Value</td><td style=";">Result wanted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">4GEE Data 2GB</td><td style=";">2GB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">8GB Data and WiFi</td><td style=";">8GB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">4GEE Data 2GB</td><td style=";">2GB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">16GB Data and WiFi</td><td style=";">16GB</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F7</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">MID(<font color="Green">" "&E7,SEARCH(<font color="Purple">"GB",E7</font>)-1,5</font>),4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

bekyleigh

New Member
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.

Thankyou for your help! :)
 

Fluff

MrExcel MVP, Moderator
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
 

Some videos you may like

This Week's Hot Topics

Top