npotapchuk
New Member
- Joined
- Nov 30, 2016
- Messages
- 4
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
<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>
</tbody>
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
Account # | Security ID | Security Type | |
EEM 161202C00036500 | 1234 | EEM161202C36.5 | Option |
#VALUE! | 1234 | SPXW161202C2185 | Option |