Remove 1st character and then read only next 7 characters

[kailashkotak91]

Hello,

I have a field where the data is very inconsistent. I want to remove the 1st character and ready the next 7 characters from there and call the blanks N/A. For example

Resource ID

[1234567 14]haslkdjasl

I want to pull only 1234567 from the resource ID field and parse out [ ] and remaining text.

Regards,

[Digvijay_Singh · Accepted solution]

May be this -

Mid(ResourceID,2,8) as ResourceID

[kailashkotak91]

Thanks for your reply! Appreciate it.

Just wanted to confirm, the data quality is really bad, i have few entries that directly does not have '[' in the beginning.

Can it be something like if(resource ID starts with [ then Mid(ResourceID,2,8) as ResourceID else if(resource ID starts with a number then some condition...

Can you help with this ?

Thanks in advance

Regards,

Kailash Kotak

[Digvijay_Singh]

Something like this may be -

if(left(ResourceID,1)='[',Mid(ResourceID,2,8),

     if(Match(Left(ResourceID,1),0,1,2,3,4,5,6,7,8,9),<Write here other expression like Mid>))

[Digvijay_Singh]

If you have situations like sometimes first character is number and sometimes '[' then this might also work -

Replace(Mid(ResourceID,1,8),'[','') as ResourceID