Data Extract Help

[npotapchuk]

Good Morning
I was trying to working to extract a specific code of data based on information that is provided to me in a file that I cannot manipulate.

What I am trying to do is have column A format based on criteria on column C. I am given the below information in B-D was was able to set the new format in column A, however was struggling to use one formula to drag down as row 2 has 4 characters and not 3 before the dates/numbers that I need to extract.

I set up the below formula which works to format in Column A but am struggling to set up a new statement to represent to pull the new data if there are 4 characters and not 3.

Any help would be greatly appreciated.

IF(OR(ISBLANK(B2),D2<>"Option"),"",CONCATENATE(LEFT(C2,3)," ",RIGHT(LEFT(C2,10),7),CONCATENATE(IF(LEN(RIGHT(C2,LEN(C2)-10)*1000)=5,"000",IF(LEN(RIGHT(C2,LEN(C2)-10)*1000)=6,"00",IF(LEN(RIGHT(C2,LEN(C2)-10)*1000)=7,"0"))),RIGHT(C2,LEN(C2)-10)*1000)))



Column A Column B Column C Column D
[TABLE="width: 356"]
<colgroup><col width="122" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4461;"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;" span="3"> <tbody>[TR]
[TD="class: xl72, width: 122, bgcolor: transparent"][/TD]
[TD="class: xl69, width: 117, bgcolor: #A6A6A6"]Account #[/TD]
[TD="class: xl69, width: 117, bgcolor: #A6A6A6"]Security ID[/TD]
[TD="class: xl69, width: 117, bgcolor: #A6A6A6"]Security Type[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]EEM 161202C00036500[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1234[/TD]
[TD="class: xl68, bgcolor: transparent"]EEM161202C36.5[/TD]
[TD="class: xl74, bgcolor: #FFC000"]Option[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent, align: center"]#VALUE![/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1234[/TD]
[TD="class: xl68, bgcolor: transparent"]SPXW161202C2185[/TD]
[TD="class: xl68, bgcolor: transparent"]Option[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

In words, what are you trying to achieve?

 

[npotapchuk]

I would like to have a formula that looks like in Column A that would should be the same result in Row 2 SPWX161202C02185000 which is the same as the result in Row 1 but because the Sec ID shows 4 charactes (SPWX) and not 3 characters (EEM) before the date it errors out.

 

[admiral100]

Hi,

Maybe this

=IF(OR(ISBLANK(B2),D2<>"Option"),"",LEFT(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789"))-1)&MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),SEARCH("C",C2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")))&"C"&MID(C2,SEARCH("C",C2)+1,99)*1000)

 

[npotapchuk]

That formula works for the SPWX but when now dragged to the cell referencing EEM (3 characters) you get an error. Believe I just need to set up an if/than statement for the two. Any thoughts?

 

[Fazza]

for those cases, how about
=MID(C2,5-ISNUMBER(MID(C2,4,1)*1),255)