Returning left part of cell before a character
 

I have a text file report that is not customizable. I import that into
Excel. There is a field where relevant data ends after the colon
character ":". Can someone help me with a formula I can put in column C
that evaluates the corresponding cell in column B and brings back all
text to the left of the colon mark?


Examples:
Column B

freddy: 45345 sldf
jeff: 45422 fdflh
sam: bfd4454

In this case I want to return the characters to the left of the colon:
freddy
jeff
sam

Thanks in Advance!
Alan

Answer: Returning left part of cell before a character
 

Hi Alan,

Sure, I can help you with that. You can use the

Formula:

---

LEFT 


---

and

Formula:

---

FIND 


---

functions in Excel to extract the text before the colon.

Here's the formula you can use in cell C1:

1. Formula:

   ---

   =LEFT(B1,FIND(":",B1)-1) 


   ---

This formula will find the position of the colon in cell B1 using the

Formula:

---

FIND 


---

function, and then extract the text to the left of the colon using the

Formula:

---

LEFT 


---

function.

You can then copy this formula down to the rest of the cells in column C to extract the text before the colon for each corresponding cell in column B.

Let me know if you have any questions or if there's anything else I can help you with.

Best regards,
[Your Name]

Hi there,

You can use the formula

=MID(B1,1,FIND(":",B1)-1)

This will bring back all the text before the colon.

Hope that helps.

Naz,
London

"Alan" wrote:

I have a text file report that is not customizable. I import that into
Excel. There is a field where relevant data ends after the colon
character ":". Can someone help me with a formula I can put in column C
that evaluates the corresponding cell in column B and brings back all
text to the left of the colon mark?


Examples:
Column B

freddy: 45345 sldf
jeff: 45422 fdflh
sam: bfd4454

In this case I want to return the characters to the left of the colon:
freddy
jeff
sam

Thanks in Advance!
Alan

Alan

DataText to ColumnsDelimited by other. Enter a colon. Next.

Select the right-hand column and "do not import column". Finish.


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 14:35:49 -0600, Alan wrote:

I have a text file report that is not customizable. I import that into
Excel. There is a field where relevant data ends after the colon
character ":". Can someone help me with a formula I can put in column C
that evaluates the corresponding cell in column B and brings back all
text to the left of the colon mark?


Examples:
Column B

freddy: 45345 sldf
jeff: 45422 fdflh
sam: bfd4454

In this case I want to return the characters to the left of the colon:
freddy
jeff
sam

Thanks in Advance!
Alan

For a formula

=LEFT(B2,FIND(":",B2)-1)

Could also use SEARCH instead of FIND. See Help for the differences (which
aren't relevant here).

On Sat, 22 Jan 2005 14:35:49 -0600, Alan wrote:

I have a text file report that is not customizable. I import that into
Excel. There is a field where relevant data ends after the colon
character ":". Can someone help me with a formula I can put in column C
that evaluates the corresponding cell in column B and brings back all
text to the left of the colon mark?


Examples:
Column B

freddy: 45345 sldf
jeff: 45422 fdflh
sam: bfd4454

In this case I want to return the characters to the left of the colon:
freddy
jeff
sam

Thanks in Advance!
Alan

On Sat, 22 Jan 2005 14:35:49 -0600, Alan wrote:

I have a text file report that is not customizable. I import that into
Excel. There is a field where relevant data ends after the colon
character ":". Can someone help me with a formula I can put in column C
that evaluates the corresponding cell in column B and brings back all
text to the left of the colon mark?


Examples:
Column B

freddy: 45345 sldf
jeff: 45422 fdflh
sam: bfd4454

In this case I want to return the characters to the left of the colon:
freddy
jeff
sam

Thanks in Advance!
Alan

Assuming the fields are in columns, you can use the Data/Text to Columns wizard
and use the colon as the delimiter. (Select Other and type in a colon : in the
box next to it).


--ron

In case you have to apply a formula...

=TRIM(LEFT(A2,SEARCH(":",A2&" :")-1))

which would handle entries with no colon and empty cells in A.

Alan wrote:
I have a text file report that is not customizable. I import that into
Excel. There is a field where relevant data ends after the colon
character ":". Can someone help me with a formula I can put in column C
that evaluates the corresponding cell in column B and brings back all
text to the left of the colon mark?


Examples:
Column B

freddy: 45345 sldf
jeff: 45422 fdflh
sam: bfd4454

In this case I want to return the characters to the left of the colon:
freddy
jeff
sam

Thanks in Advance!
Alan