Concatenate

[EvansB2]

Hi there,

I have a numbers and text in a single cell (call it A1), namely:

"L-HQ-0001,Entrance Foyer"

I would like to split the string so that in cell B1 it would return the following result:

"L-HQ-001"

Therefore, removing the text along with the comma.

Any assistance would be gratefully received as I have around 3,000 entries to split.

Regards

Ben

 

[Scott T]

Try
(I assume your result was a typo and you do not want to remove a zero)

=LEFT(A1,SEARCH(",",A1)-1)

 

[Joe4]

If they are all in the same column, you can do this easily with Text to Columns.
Just choose the comma as the delimiter, and elect to "Do not import column (skip)" the second column.

If they are all over the place and you need a formula, try this:

=LEFT(A1,FIND(",",A1)-1)

 

[EvansB2]

Great works a treat.

And yes it was a typo!

Thanks

Ben

 

[EvansB2]

They all appear uniform.

So what is the difference between 'FIND' and 'SEARCH' in this instance?

Regards

Ben

 

[Joe4]

One is case sensitive, the other is not.
See: http://www.excel-easy.com/examples/find-vs-search.html
Choose whichever one works best for you.

 

[Scott T]

The difference between find and search is find IS case sensitive search is not. Since this is a "," it does not matter here.

Search is also at the top of the tool tip so to use it you can just type in =s and search will be highlighted and you can tab to use the function.

 

[Joe4]

[COLOR=#333333]Since this is a "," it does not matter here.[/COLOR]

LOL! I confused this with another one I was helping with in which we were searching for a specific word!
Obviously, case doesn't matter with punctuation!