I am using the =CONCATENATE function, and one of the cells I'm calling on I only want to take the value in the cell up to the first semi-colon. Is this possible, and if so how?
1 Answer
You can use a combination of LEFT
and SEARCH
to pull back everything up to the semicolon.
LEFT(A1,SEARCH(";",A1) -1)
This example assumes the cell you are trying to get the first block from is A1
. LEFT
is used to get everything from the left-most character up to a certain point, and SEARCH
will return the character position of the first occurrence of ;
. The -1
is so that the semicolon is not included in the resulting text.
-
I think this may solve my problem, but I am struggling to use that in my concatenation function as I get an error regarding ='s. This is what my function looks like: =CONCATENATE("<a href=""url/", J5, ".asp"">hyperlink text</a>", "</br></br>", B5) I need to do the search to what is J5 in my function. Can you help?– user2170261Mar 18, 2013 at 20:04
-
Just replace the
J5
in your formula withLEFT(J5,SEARCH(";",J5)-1)
Mar 18, 2013 at 20:06 -
I thought it was working, i jsut get #VALUE! I did exactly as you put– user2170261Mar 18, 2013 at 20:14
-
Hm. Works fine on mine when I paste your formula into a sheet and add in my formula as described. Did you get the commas and parenthesis right? When I first posted the comment, I left off a comma. #VALUE! in this formula usually means the search term (
;
) is not found. Mar 18, 2013 at 20:15