Changing number & text to just number

[ArPharazon]

I have a spreadsheet with a column containing number & text (see image).

Any way to convert to just the number, or truncate it to the first 1 or 2 characters?

The column isn't locked, I can convert another column from one date format to another.

 

[Rick Rothstein]

I have a spreadsheet with a column containing number & text (see image).

Any way to convert to just the number, or truncate it to the first 1 or 2 characters?

The column isn't locked, I can convert another column from one date format to another.

Your image is not showing, rather, there is a message from "imageframe" saying the bandwith has been exceeded. Can't you just type in some before and after examples for us?

 

[ArPharazon]

Stupid imageframe.

There's several different things in a given cell: "1 pallet", "2 pallets", "1 crate", "1 unit", etc.

I'd like to keep the number and get rid of the text if possible.

Thank you for the reply.

 

[Rick Rothstein]

There's several different things in a given cell: "1 pallet", "2 pallets", "1 crate", "1 unit", etc.

I'd like to keep the number and get rid of the text if possible.

If those examples are truly representative of what all your data will look like, then this simple formula should work for you...

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

 

[ArPharazon]

If those examples are truly representative of what all your data will look like, then this simple formula should work for you...

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

I'll be back to work on Monday & give that a shot then.

Thanks for the reply, I'll let you know what happens.

 

[Cant89]

I have similar problem, but I'd also like to keep second part.
For example in this record
ID2412:CAD
I'd have ID2412 in one field and CAD in the other (ignoring colon). Any simple formula to do that?

 

[mretyk]

You can try doing =LEFT(A1,FIND(":",A1)-1) in let's say B1 and then =SUBSTITUTE(A1,B1&":",) in C1.

 

[hiker95]

Cant89,

How about this?

Excel 2007
	A	B	C
1	ID2412:CAD	ID2412	CAD
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(":",A1)-1)
C1	=RIGHT(A1,LEN(A1)-FIND(":",A1))

 

[Rick Rothstein]

How about this?

Excel 2007

	A	B	C
ID2412:CAD	ID2412	CAD

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #E0E0F0"]B1[/TH]
[TD="align: left"]=LEFT(A1,FIND(":",A1)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]C1[/TH]
[TD="align: left"]=RIGHT(A1,LEN(A1)-FIND(":",A1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Or, for C1, possibly this...

=MID(A1,FIND(":",A1)+1,99)