1

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 1

3

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.

4
  • 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?
    – user2170261
    Mar 18, 2013 at 20:04
  • Just replace the J5 in your formula with LEFT(J5,SEARCH(";",J5)-1)
    – techturtle
    Mar 18, 2013 at 20:06
  • I thought it was working, i jsut get #VALUE! I did exactly as you put
    – user2170261
    Mar 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.
    – techturtle
    Mar 18, 2013 at 20:15

You must log in to answer this question.