How to get all characters to the left of another character

  • Thread starter Thread starter Sherry
  • Start date Start date
S

Sherry

Let's say I have a cell (A1) with 12345:67890
If I want to get everythingto the left of the : I could do =Left(A1,5)
That's wored fine until now. Some of my data comes to me like this:
012345:67890 or 00012345:6789000000

How can I get everything to the left of the : ????
 
Sherry said:
Let's say I have a cell (A1) with 12345:67890
If I want to get everythingto the left of the : I could do =Left(A1,5)
That's wored fine until now. Some of my data comes to me like this:
012345:67890 or 00012345:6789000000

How can I get everything to the left of the : ????
Click to expand...

=LEFT(A1,FIND(":",A1)-1)
 
Let's say I have a cell (A1) with 12345:67890
If I want to get everythingto the left of the : I could do =Left(A1,5)
That's wored fine until now. Some of my data comes to me like this:
012345:67890 or 00012345:6789000000

How can I get everything to the left of the : ????
Click to expand...


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


--ron
 
Another option is select all your data, do Data / text To Columns / Delimited /
Tick the 'Other' box and put : into it. Thne hit OK - Click on the second
column and select 'Do Not Import Column'. Hit Ok and you are done.
 
This gives me a #VALUE! What might I be doing wrong?
Click to expand...

1. Applying the formula to a cell which does not have a ":".
2. Mistyping the formula so the ":" is not a ":"

Copy/Paste the formula, and the data upon which it is giving the error message
so we can see exactly what you are doing.


--ron
 
Back
Top