Left 12 or left 11?

[Rocket28]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Is there a way I can write a formula to give me the data 12 from the left or 11 from the left?<o></o>
<o></o>
I currently download streams of data that have numerous digits, but it is only the first 11 or 12 that I am interested in for coding. <o></o>
<o></o>
The data is separated by an hyphen, and I currently have to use the formula =LEFT(T4,12) for 12 digit numbers which then have a hyphen after it, and then find the items that have 11 digits and then a hyphen, and I use =LEFT(T5,11).<o></o>
<o> </o>
I.e. 0100-0001DCA-PL1091 and 3605-0001WC-2<o></o>
<o> </o>
This, however, takes a very long time, as there are thousands of lines, and they are all mixed in to one another.<o></o>
<o> </o>
Is there a formula I can write so it can give me 11 or 12 digits depending on where the hyphen appears<o></o>
<o> </o>
Thanks.<o></o>
<o></o>

 

[Jonmo1]

Try

=LEFT(A1,IF(MID(A1,12,1)="-",11,12))

or

=LEFT(A1,11+(MID(A1,13,1)="-"))

 

[Gerald Higgins]

You can almost certainly do this.
Is it ALWAYS the second hyphen ?
If yes, try this

=LEFT(T5,FIND("-",T5,FIND("-",T5,1)+1)-1)

 

[MrKowz]

I am assuming you need the data that is just to the left of the second hypen?

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">0100-0001DCA-PL1091</td><td style=";">0100-0001DCA</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">3605-001WC-2</td><td style=";">3605-001WC</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,FIND(<font color="Red">"$$",SUBSTITUTE(<font color="Green">A1,"-","$$",2</font>)</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[JamesW]

Hi,

Try:

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

 

[mikerickson]

Perhaps =SUBSTITUTE(LEFT(A1, 12), "-", "", 2)

 

[Rocket28]

Jon,

Thasnk, but unfortunalty yours doesnt works.

Gerald,

Thanks heaps, works a treat!

Cheers.

 

[Jonmo1]

Jon,

Thasnk, but unfortunalty yours doesnt works.

Gerald,

Thanks heaps, works a treat!

Cheers.

In what way does my suggestion not work?
Which one did you try?

The only difference is that I used A1 as the cell containing your string, instead of T4 or T5....Sorry.


Works for me

Excel Workbook
	T	U	V
4	Your Data	My 1st formula	My 2nd formula
5	0100-0001DCA-PL1091	0100-0001DCA	0100-0001DCA
6	3605-0001WC-2	3605-0001WC	3605-0001WC
Sheet1