Solved If number is in text string,then return value from naborcell

December 12, 2014 at 12:20:31
Specs: Windows 7
Hello all

I have a few text string like this: "Text to 1200, 1300, 1500" in column A.
In column B in the same line as the text string, I have the text that those numbers will receive.

In another sheet (sheet2) I have a some rows with the numbers 1200, 1300, 1400, 1500 and so on in column A. One number in each row.

I would like the text to be inserted in column D, in the right row corresponding with the numbers in column A.

It looks a bit like a VLOOKUP thing, but then again, how would I search the text string for those numbers and compare them to the numbers in each row?

Thanks in advance

/Jacob


See More: If number is in text string,then return value from naborcell

Report •


✔ Best Answer
December 22, 2014 at 09:48:33
Why do you want to concatenate a space on the end of the strings?

It is being "simulated" within the formula I suggested without any need to change the actual string in the cell.

The formula uses Box!$A$8&" " which Excel evaluates to be the contents of Box!$A$8 with a space on the end.

You do realize that my suggestion is a single solution, not solutions, don't you?

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

message edited by DerbyDad03



#1
December 12, 2014 at 14:30:21
I'm confused.

You have multiple numbers in the text string in Column A and you want to return the Sheet2 text associated with each of those numbers into Sheet 1 Column D?

Multiple pieces of text from Sheet 2 into the same cell in Sheet 1 Column D?

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


Report •

#2
December 12, 2014 at 23:15:20
No, it would look like this:

Sheet2, Column A has the text strings with numbers in them.

Sheet2, Column B has the individual text-bits, that can relate to one or more numbers, thats why the text strings has more than one number in them.

Sheet1, Column A has a number in each row with the associated numbers in them, like 1200 in row 1, 1300 in row 2 and so on.

I would like to get the individual texts from sheet2 and into the right row in sheet1 depending on if the number is in the text string.



Report •

#3
December 13, 2014 at 05:12:49
Perhaps it would best if you posted an example of your data, using sheet names, row numbers and column letters. The instructions found via the link at the bottom of this post will explain how to post data in this forum so that the column format is retained.

As far as I can tell, you switched sheet numbers from what you said in your original post vs. what you said in your latest response. Posting a short example of your input data, along with the expected output, would really help eliminate the confusion.

Thanks.

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


Report •

Related Solutions

#4
December 15, 2014 at 07:46:46
Sheet1 looks like this:

	A	        B       	C
1	CustomID	ID number	Text
2	1200		
3	1300		
4	1400		
5	1500		

Sheet2 looks like this:

	A	B	C	D	E             	                       F
1	ID nr	Text to:			        	               Text
2	1	Text to number 1200, 1300 and 1500		               Hello, how are you doing.
3						
4	2	Text to number 1400, 1600                                      We can talk later.

To explain:

The text in column F in Sheet2 is the text I would like to insert in Sheet1 in the rows which correspond with the CustomID numbers that are in the text-string in Sheet2 column B.

message edited by JacobJ


Report •

#5
December 16, 2014 at 13:06:20
Okay I have got as far as checking the text string for the ID nr and return the text Hello, how are you doing, if the ID nr is in that string of text.

This I have made possible with this code:

=IFERROR(INDEX(PAS!$E$1:PAS!$E$20;MATCH((IF(NOT(ISERROR(SEARCH(A2;PAS!B2)));PAS!B2; "No Match"));PAS!$B$1:PAS!$B$20;0));"Error")

The only problem is, that this formula only searches for a match in cell PAS!B2.

I would like the code to search the range PAS!B2:PAS!B100 for a match. I have looked at an array version, but havnt been able to get it working.

Any suggestions on how to make it search the PAS!B2:PAS!B100 range?


Report •

#6
December 16, 2014 at 13:26:20
How hard would it be to add a Helper Column on Sheet1?

If the corresponding ID nr for each of the CustomID values was present in Sheet1, a simple VLOOKUP would work.

e.g. If Sheet 1 looked like this, you could VLOOKUP the ID nr in Column A of your other sheet

         A           B            C         D
1       ID nr     CustomID    ID number    Text
2         1         1200
3         1         1300
4         2         1400
1         1         1500

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


Report •

#7
December 17, 2014 at 01:56:51
Yes, thats how I do it at the moment, but that I would like to change.

I want to just specify in the text to string, which CustomID's that should get the text string located in sheet2 column F.

I have found out that I can insert a code like this, but this is a code that doesnt count for any changes in how many text to strings there are and the code is limited to check only the cells specified in the code:

=IFERROR(INDEX(PAS!$E$1:PAS!$E$100;MATCH((IF(NOT(ISERROR(SEARCH(A5;PAS!$B$2)));PAS!$B$2;(IF(NOT(ISERROR(SEARCH(A5;PAS!$B$3)));PAS!$B$3;(IF(NOT(ISERROR(SEARCH(A5;PAS!$B$4)));PAS!$B$4;(IF(NOT(ISERROR(SEARCH(A5;PAS!$B$5)));PAS!$B$5;(IF(NOT(ISERROR(SEARCH(A5;PAS!$B$6)));PAS!$B$6;(IF(NOT(ISERROR(SEARCH(A5;PAS!$B$7)));PAS!$B$7;"No Match"))))))))))));PAS!$B$1:PAS!$B$100;0));"No text match for this CustomID number")

A5 is the customID number to search for.


Report •

#8
December 17, 2014 at 03:55:44
Would you be willing to use a User Defined Function (UDF)?

A UDF is written in VBA and entered as a function in a cell. Any user using the UDF would need to have macros enabled on their system in order for the function to work.

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


Report •

#9
December 17, 2014 at 07:37:51
Is it enough that the excel file is a template with macro type? Or maybe if the file is located in a place that is defined as a trusted location?

Because if not, then I am not sure that I am able to use it on our LAN network.


Report •

#10
December 17, 2014 at 21:34:05
A "template" wouldn't help. If a trusted site was used, that site would have to be trusted by each individual system.

The goal is to force users (or an authorized representative, such as the company IT folks) to make a conscious decision about allowing macros to be run. If code were allowed to be run without the user's "consent" then anyone could put malicious code in a spreadsheet and cause damage.

There are ways to "help" users make the decision as described here:

http://datapigtechnologies.com/blog...

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


Report •

#11
December 18, 2014 at 13:04:16
Hmm okay, I will have to run some tests on our system to see how much we can do. If every user (or our IT folks) would set the trusted location on each individual computer, then macros from that document can be run, isnt that right?

Report •

#12
December 22, 2014 at 04:21:34
Okay I am having some difficulties with the IT folks at my Work and I am put on hold untill further notice.

Ive worked this out and it does work. But a problem is, that it also returns TRUE (it returns the information) when the match isnt a exactly match. If it searches for 1300 in a text string where 13000 is present it returns true. Have can it only return true if it is a exact match? I have a hunch that its a problem with the search function. Any suggestions?

=ISERROR(
INDEX(Box!$D$1:Box!$D$100;MATCH((
IF(NOT(ISERROR(SEARCH($B$2;Box!$A$8)));Box!$A$8;
"No Match"));Box!$A$1:Box!$A$100;0));"ERROR")


Report •

#13
December 22, 2014 at 08:06:36
I get an error when I try to use your formula: "You've entered too many arguments for this function"

Excel then highlights the word "Error"

In any case...

re: If it searches for 1300 in a text string where 13000 is present it returns true.

If I look at this string, I see that the numbers are either followed by a space or a comma, except for the last number.

Text to number 1200, 1300 and 1500

Try incorporating this into your formula:

=OR(NOT(ISERROR(SEARCH($B$2&",",Box!$A$8))),
NOT(ISERROR(SEARCH($B$2&" ",Box!$A$8&" ")))))

What this does is SEARCH for B2 in 2 different ways:

The first OR clause searches Box!$A$8 for "B2 with a comma concatenated onto it" to find values in the middle of Box!$A$8.

Text to number 1200, 1300 and 1500

It will not return TRUE for 12000, since 1200, is not a match for 12000,

The second OR clause searches for "B2 with a space concatenated onto it" in "Box!$A$8 with a space concatenated onto it". This should find B2 when it precedes the and as well as when the string is found at the end of Box!$A$8.

Text to number 1200, 1300 and 1500

The detail: Let's say B2 contained 1500. My suggested formula will search for 1500&" " in Text to number 1200, 1300 and 1500&" ". If the value before the word and was 1500, it would return TRUE because of the space. It would not return TRUE for 15000, once again because of the space. If the last value was 1500, it would return TRUE because we concatenated a space onto the end of the entire string, so 1500&" " will be found. However, if the last value was 15000, it would not return TRUE because 15000&" " is not a match for 1500&" ".


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

message edited by DerbyDad03


Report •

#14
December 22, 2014 at 09:35:29
Very fine solutions, I think that will cover all of the possible senarios with this text string. Is there an easy way to concatenate a space at the end of those stings? I can see it would be easy to make a helper column that would do that, but are there any other solutions? Like formatting or something like that?

Report •

#15
December 22, 2014 at 09:48:33
✔ Best Answer
Why do you want to concatenate a space on the end of the strings?

It is being "simulated" within the formula I suggested without any need to change the actual string in the cell.

The formula uses Box!$A$8&" " which Excel evaluates to be the contents of Box!$A$8 with a space on the end.

You do realize that my suggestion is a single solution, not solutions, don't you?

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

message edited by DerbyDad03


Report •

#16
December 22, 2014 at 11:09:49
Oh I see, thats why it works without me adding anything to the text string..hehe

Yes I know it is one solution, but it solves both the problem if the number is followed by a "," and if the number is the last one in the text string. I have made a few tests and it all seems to work fine, but I will have to build it into my final code tomorrow at work. I will post back how it went, but thanks for now!


Report •

Ask Question