Solved Return of a value

August 31, 2011 at 01:35:54
Specs: Windows XP
Hi there,
my problem is to return a specific value from a range of cell that contains also text. There are several values which apears in these range and I want to return them in other range of cell. Hope I explain in well. Thanks anyway.

See More: Return of a value

Report •


✔ Best Answer
September 2, 2011 at 04:05:32
Your A1 and B2 references don't make sense.

I assume that this is what you meant:

          A                  B
1  Food 400 (27)
2  Food 350 (12)
3  Food 200(14)
4  Food 100 (9)

You have (at least) 3 options:

Select the data in Column A and uses these steps:

Data...Text to Columns...Delimited...Next...Space

You should get:

     A        B       C
1  Food      400     (27)
2  Food      350     (12)
3  Food      200     (14)
4  Food      100     (9)

or, you could copy Column A into Column B, perform the Text To Columns on Column B and then delete the columns you don't need. This will leave the data in Column A intact, as would using this formula, which worked on the example data that you posted.

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1))-2)

          A                  B
1  Food 400 (27)           400
2  Food 350 (12)           350
3  Food 200(14)            200
4  Food 100 (9)            100

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 31, 2011 at 01:44:58
You will need to explain a lot better, assuming you are talking about Excel?

How are you calling the function, how do you want the information returned? where is the returned information going to be stored or displayed?

if for example

A1 = "Hello"
B1 = "To"
C1 = "All"

you can use a formula in D1 like

=A1 & " " & B1 & " " & C1

D1 will then become Hello To All

If you wanted to do this in a macro it is also possible but you will need to provide some more information


Report •

#2
August 31, 2011 at 04:09:21
Ok some more info :
Yes we are tolking about Excel. In A1 I have a text with some values - "Food - 400 g " , in A2 - "Food - 350 g" etc. So I want to take only values from the cells (400, 350, etc. ) and to use them for further calculation. In some of the cell there are more values that these that I need (example - "Food - 200 g (27)" ), but I want to take a specific values - quantity for example. So I have a list with the quantities(400,300,250,200 etc.) but I don't know a formula which can return only a specific values from the cells. I think to use IF but should be with another formula. Hope now its clear. Thank you.

Report •

#3
August 31, 2011 at 05:02:04
Please click on the blue line at the bottom of this post and read the instructions found via that link.

Then post a few examples of the strings from which you need to extract the values. If the strings are very different from each other, i.e. 1 word before the value in some, 2 words before the value in others, etc. make sure that you post enough examples so that we can get a feel for the various types of strings we are dealing with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 2, 2011 at 00:24:11
A1           B2
Food 400 (27)
Food 350 (12)
Food 200(14)
Food 100 (9 

So it's always like this - First is the text, then are values - first value is the weight(i need to extract this) and then in the brackets are the items in one box. I need the values of the weight in column "B". Thanks

Report •

#5
September 2, 2011 at 04:05:32
✔ Best Answer
Your A1 and B2 references don't make sense.

I assume that this is what you meant:

          A                  B
1  Food 400 (27)
2  Food 350 (12)
3  Food 200(14)
4  Food 100 (9)

You have (at least) 3 options:

Select the data in Column A and uses these steps:

Data...Text to Columns...Delimited...Next...Space

You should get:

     A        B       C
1  Food      400     (27)
2  Food      350     (12)
3  Food      200     (14)
4  Food      100     (9)

or, you could copy Column A into Column B, perform the Text To Columns on Column B and then delete the columns you don't need. This will leave the data in Column A intact, as would using this formula, which worked on the example data that you posted.

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1))-2)

          A                  B
1  Food 400 (27)           400
2  Food 350 (12)           350
3  Food 200(14)            200
4  Food 100 (9)            100

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 2, 2011 at 04:46:11
Thanks for your help. The option with Text to Column works, but when I try using the formula that you made Excel didn't accept it like a formula(I'm using Excel 2007). I acctualy don't understand your formula what you mean in these" " (FIND(" ",A1)) Should I put someting there? Thank you.

Report •

#7
September 2, 2011 at 07:02:36
What do you mean by "Excel didn't accept it like a formula"?

Did it pop up the message that starts with "The formula you typed contains an error" or did it return an error value in the cell itself?

I can't test it in 2007 right now because I don't have that version on the machine I'm using at thsi time, but if I Copy-Paste the formula right from my post into 2003, it works as expected. There is nothing in the formula that shouldn't work in 2007, but I won't be able to test it until tonight.

That said, here is how the formula works:

Let's start by breaking down the 2 functions used:

MID(text, start_num, num_chars)

This function will look at the "text" in the text argument, which can be an actual text string ("Hello") or a reference to a cell (A1).

It will then start at the position specified by the start_num argument and return the number of characters specified by the num_chars argument.

Let's look at this example from your data:

              A
1   Food 400 (27)

=MID(A1, 1, 3) will return Foo: 3 characters starting at character position 1

=MID(A1, 6 , 3) will return 400: 3 characters starting at character position 6
FIND(find_text, within_text, start_num)

find_text is the text string you are trying to find within the string designated by the within_text argument. As with the MID function, both of those arguments can be either an actual text string or a cell reference.

start_num is the character position within within_text where you want FIND to start looking.

e.g. =FIND("A", "123ABCA" ,1) will return 4 since A is in the 4th position if we start at position 1.

However, if we use a start_num of 5, it will return 7, since it will ignore the first A and find the last A which is in postion 7.

OK, so putting this all together...

Food 400 (27)

If I thought that Food would always be a 4 character string and that the value you want returned was always going to be a 3 character string, I could simply use this to return 400:

=MID(A1, 6, 3)

However, I am assuming that the length of the first string (Food) will vary as will the length of the value you want returned (400), therefore I have to determine the position of the first and last character of that value (e.g. 400).

That's where the FIND function comes into play.

When I look at the string, I see a space before the 400 and a space afterwards. So I use FIND to find the position of the 1st space and use it again to find the position of the 2nd space, I can determine the starting and ending position of the value (e.g. 400). Since I don't know what the 1st or last character of the value will be, but I do know that there is a space before and after it, I can FIND those spaces and use the values returned as the arguments for the MID function.

=FIND(" ", A1, FIND(" ", A1))

This says to find a space (" ") in A1, but start looking from the postion of the 1st space, so that it returns the position of the 2nd space.

So once we find the position of the 1st space and the position of the 2nd space we adjust the values returned for those postions to determine the exact length of the value between those spaces and use that as the num_chars argument for the MID function.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question