Left Mid Right help

[rex759]

Hello,
I’m attempting to extract the number after FW, (9,10, 11) so I can add them to something else. The length of the cell including spaces is 17 for single digits (1-9) and 18 for double digits (10-15)

I tried this which works for double digits but not getting the correct results.

Any ideas?

Thanks

South Atlantic.xls
	B	C	D	E
1
2	2/1/2010(FW1)		17
3	2/8/2010(FW2)		16
4	2/15/2010(FW3)		15
5	2/22/2010(FW4)		14
6	3/1/2010(FW5)		13
7	3/8/2010(FW6)		12
8	3/15/2010(FW7)		11
9	3/22/2010(FW8)		10
10	3/29/2010(FW9)		9
11	4/5/2010(FW10)		10
12	4/12/2010(FW11)		11
13	4/19/2010(FW12)		12
14	4/26/2010(FW13)		13
15	5/3/2010(FW14)		14
16	5/10/2010(FW15)		15
17	5/17/2010(FW16)		16
18	5/24/2010(FW17)		17
19	5/31/2010(FW18)		18
20	6/7/2010(FW19)		19
Sheet1

 

[Weaver]

try:

=TRIM(SUBSTITUTE(MID(A1,FIND("FW",A1)+2,10),")",""))

 

[Willem!]

Hi,

For double-digits use:

=LEFT(RIGHT(B11,3),2)

For single-digits use:

=LEFT(RIGHT(B11,2),1)

HTH

 

[BAlGaInTl]

This should work, but one of the other solutions seems neater...

=MID(MID(A2,FIND("(",A2),255),FIND(" ",MID(A2,FIND("(",A2),255))+1,FIND(")",MID(A2,FIND("(",A2),255))-LEN(MID(A2,FIND("(",A2),255))+1)

 

[Willem!]

Correction:

=LEFT(RIGHT(S3,3),2)

works for double- & single-digits, in further calculations the extra space (e.g. " 9") is omitted.

 

[rex759]

WOW! You guys are awsome!!

Thank you all!

 

[Willem!]

Glad we could assist!

 

[rex759]

WOW! you guys are awsome!

Thanks!

 

[mnemoni]

The following formula also works ... for both mddyyyy and mmddyyyy:

=IF(A2>0,LEFT(A2,IF(LEN(A2)=7,1,IF(LEN(A2)=8,2)))&"/"&LEFT(RIGHT($A2,6),2)&"/"&RIGHT(A2,4),"")

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Original</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=64>Formula</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3012007</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" x:fmla='=IF(A2>0,LEFT(A2,IF(LEN(A2)=7,1,IF(LEN(A2)=8,2)))&"/"&LEFT(RIGHT($A2,6),2)&"/"&RIGHT(A2,4),"")'>3/01/2007</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" x:str="" x:fmla='=IF(A3>0,LEFT(A3,IF(LEN(A3)=7,1,IF(LEN(A3)=8,2)))&"/"&LEFT(RIGHT($A3,6),2)&"/"&RIGHT(A3,4),"")'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>6121947</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" x:fmla='=IF(A4>0,LEFT(A4,IF(LEN(A4)=7,1,IF(LEN(A4)=8,2)))&"/"&LEFT(RIGHT($A4,6),2)&"/"&RIGHT(A4,4),"")'>6/12/1947</TD></TR></TBODY></TABLE>

Thanks for helping me develop that formula Mr. Excel -- I couldn't have done it without you smart people.