 # Extract three sets of numbers from one cell

• Hi All,

My problem is that I have around 300 cells (same column) which contain endless amounts of data and I need to extract two sets of numbers from them. Here's an example:

"Orginal Message as follows:

------------------------

Reads - B = 6742 F= 3027"

(This is a shorter version compared to what is in the cells but it gives you an idea)

The challenge is that all the cells have different lengths of data in them. I need the numbers in bold putting in seperate columns.

I've tried the 'text to columns' function however because the cells are varied in length it proves to be useless.

Is there anyone out there that could help me?

Thanks

• Re: Extract three sets of numbers from one cell

It would be best if you uploaded a sample workbook that shows several examples of the data so we can get an idea of the consistency and variation of the strings.
Will the numbers you need always be preceeded by an uppercase letter followed by the equal sign? If so, this should be fairly easy.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Extract three sets of numbers from one cell

I think I understand what you are trying to do. From your example, I put the "B = 6742 F = 3027" into cell A8. I then put the following formulas into cell B8 and C8 respectively.

B8: =MID(A8,FIND("=",A8,1)+2,FIND(" ",A8,FIND("=",A8,1)+2)-FIND("=",A8,1)-2)
C8: =MID(A8,FIND("=",A8,FIND("=",A8,1)+1)+1,LEN(A8)-FIND("=",A8,FIND("=",A8,1)+1)+1)

Simply copy the formulas for the 300+ rows that you have.

Essentially the first formula extracts the text between the first "=" and the space after the number. The second formula extracts the text after the second "=". Note that this formula assumes that you have a "= ' format in your source text.

I have attached as sample that also shows how to convert it to a number (that you can do math on).

Hope that helps.

## Files

• Re: Extract three sets of numbers from one cell

Hi Guys,

Thanks for your replies and sorry I haven't got back to you over the weekend.

I have attached an example Excel doc just to give you an idea of the data. I have also put an example table to show the way in which I would like the data to be extracted.

CurtFunk: I will have a look at your example Excel sheet and see if it is possible to adapt the formula.

The main issue is that the value could follow 'B/W:', 'B/W:', 'Black =', 'Black:', 'Black ' and this is the same for 'Colour'.

Also the length of the string will change in every cell as there are e-mails, addresses etc in there.

Thanks

Dan

## Files

• Re: Extract three sets of numbers from one cell

Hi Guys,

CurtFunk: I've had a little play with the spreadsheet you attached and it works perfectly for cells when the format is e.g. 'Black = 123456' and 'Colour =123456'. So thanks for that.

Do you know how I could adapt this for the following formats ':', '-', ' '? In my data set it seems there are lots of instances where to value follows a ':' but the issue here would be that nearly every line in the cell has a ':'. For example 'Address: ', 'Tele: ' etc.

How would I find the ':' that corresponds to 'Black', 'B/W', 'Colour', 'Total', 'Readings' etc?

Thanks

Dan

• Re: Extract three sets of numbers from one cell

05125668,

Please DO NOT quote entire posts when replying as it clutters up the thread. If you need to quote just use the specific part of the post that is relevant to your reply. It will make the thread easier to follow. Thanks.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Extract three sets of numbers from one cell

Hi Dan

Given the complexity of your data extract requirements I think that using an excel formula will exceedingly difficult (particularly if you want to avoid going blind reviewing the syntax). I think this is a good example for a user-defined-function (UDF). I am not sure your level of experience with UDF or visual basic. I developed a UDF that will allow you to pass all the different variations of the values you are looking for (i.e. "BLACK", "BLACK:", "BLACK " or "BLACK -". The function will return the associated value. You can use this function for the other lookups (like TOTAL, COLOUR, B/W etc.).

To make life easier I created the UDF and included it in the attached sample file (that was your original post). It illustrates how to use the function.

NOTE: The function assumes that there is always at least a " " before the value we are extracting. Also, the search is case-sensitive. Also, the excel sheet now has an XLSM extension (macro enabled spreadsheet).

I hope this helps ... CF

PS In the event that you have experience with UDF, I have included the code.

Those reading this thread - don't laugh at the code - but here is the code for the UDF

## Files

• Re: Extract three sets of numbers from one cell

CF,

Thanks for your work on this one.

I will have a look at the example and see if I can fully utilise your code.

With regards to my VB exposure - I did computing at college but only touched on Real Basic programing, so I'm O.K with simple programming. Nevertheless I'm willing to learn and will give it a go.

Thanks again for your help. If I have any other further questions then I will let you know.

05125668

• Re: Extract three sets of numbers from one cell

Quote from 05125668;515341

CF,
Thanks again for your help. If I have any other further questions then I will let you know.
05125668

Your welcome - anytime.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!