How do I ....

[stuartbrisgel]

Hello Y'all


Question

N47 = 32002-4211050-new


I want


M47 = 32002

and

N47 = 4211050




How do I do that?******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/3b664048/background/helpers/prefilterHelper.js">*********>

 

[Momentman]

You can use Text to columns and select "-" as delimeter

 

[stuartbrisgel]

don't follow at all ******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/5354ebc8/background/helpers/prefilterHelper.js">*********>

 

[Momentman]

See this example to understand my point above. If you have data in a column thats seperated by a a particular character, like in my example below, a hyphen....You can select the column with the data, go to DATA tab, select Text to columns and selectDelimited and select hyphen as the character to seperate by. Just follow the wizard and you will see how it works. see the table below for an example of the result when the data in column A is split

Excel 2010
	A	B	C	D
1	155625-gfhhhg-7678686	155625	gfhhhg	7678686
2	4564747-red-36363	4564747	red	36363
3	364737-green-446575	364737	green	446575
4	645858-true-47474	645858	TRUE	47474
Sheet1

If you want to use a formula for example, see an example below. there are a lot of formulas that can give you the same result

Excel 2010
	M	N	O
47	32002-4211050-new	32002	4211050
Sheet1

Cell Formulas
Range	Formula
N47	=LEFT(M47,FIND("-",M47)-1)
O47	=MID(M47,FIND("-",M47)+1,FIND("-",M47,FIND("-",M47)+1)-FIND("-",M47)-1)

 

[stuartbrisgel]

Ok great part 1 done when I copied and pasted I have this effect
[TABLE="width: 232"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]32123[/TD]
[TD]4110050[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4206210[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4278910[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4203585[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4260467

It does not see them as numbers but as texts?[/TD]
[/TR]
</tbody>[/TABLE]
******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/5354ebc8/background/helpers/prefilterHelper.js">*********>

 

[Rick Rothstein]

Ok great part 1 done when I copied and pasted I have this effect
[TABLE="width: 232"]
<tbody>[TR]
[TD="align: right"]32123[/TD]
[TD]4110050[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4206210[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4278910[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4203585[/TD]
[/TR]
[TR]
[TD]32123[/TD]
[TD]4260467

It does not see them as numbers but as texts?[/TD]
[/TR]
</tbody>[/TABLE]
******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/5354ebc8/background/helpers/prefilterHelper.js">*********>

Just add zero to the formulas Momentman posted....

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N47[/TH]
[TD="align: left"]=0+LEFT(M47,FIND("-",M47)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O47[/TH]
[TD="align: left"]=0+MID(M47,FIND("-",M47)+1,FIND("-",M47,FIND("-",M47)+1)-FIND("-",M47)-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Be warned, though, converting them to real numbers will lose any leading zeroes that might be there.

 

[Momentman]

That shouldn't happen anyways, 'cos when you do Text to columns and use the general format, it pastes Numbers(in the form of text) as numbers

 

[stuartbrisgel]

[TABLE="width: 167"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]4625629[/TD]
[/TR]
[TR]
[TD="align: right"]4626935[/TD]
[/TR]
[TR]
[TD="align: right"]4655172[/TD]
[/TR]
[TR]
[TD="align: right"]4655173[/TD]
[/TR]
[TR]
[TD="align: right"]6016460[/TD]
[/TR]
[TR]
[TD="align: right"]6052830[/TD]
[/TR]
[TR]
[TD="align: right"]6057588[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]4211050[/TD]
[/TR]
[TR]
[TD]6013938[/TD]
[/TR]
[TR]
[TD]4109854[/TD]
[/TR]
[TR]
[TD]4239601[/TD]
[/TR]
[TR]
[TD]4211573[/TD]
[/TR]
[TR]
[TD]4110050[/TD]
[/TR]
[TR]
[TD]4206210[/TD]
[/TR]
</tbody>[/TABLE]
******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/5354ebc8/background/helpers/prefilterHelper.js">*********>