0

I need to create a calculated column to get the number 7261 from the string below.

Sometimes it can be 4 digits, sometimes 5, so cant use exact character count.

I tried to use the FIND option and say Find , Serial which will work but gives me all the info before ,Serial rather than just the 4 characters 7261.

Please help.

String :

service report for notification: 404-ED-GREYN-007261, Serial 69530, 999003550472-0003

Improve this question
2
  • Hi, What is the pattern to get these numbers? i.e. is there any criteria to decide on why we have to fetch five characters instead of four characters? Should these extracted characters be always at the end of the string?
    – Rajashekhar Sheelvant
    Commented Feb 18, 2019 at 2:07
  • its actually from a email subject line..
    – marco
    Commented Feb 18, 2019 at 2:37

2 Answers 2

Reset to default
1

This will return just the four digits:

=MID( [yourColumnName], FIND("Serial", [yourColumnName] ) - 6, 4 )

This will return all six digits, including leading zeros:

=MID( [yourColumnName], FIND("Serial", [yourColumnName] ) - 8, 6 )

This will return the number (1-6 digits) without the leading zeros:

=0 + MID( [yourColumnName], FIND("Serial", [yourColumnName] ) - 8, 6 )

The above assumes the exact format from your sample and that the value will never be blank.

To deal with misformatted data or a blank:

=IF( ISERR( FIND("Serial",[yourColumnName]) ), 
     "n/a",  
     MID([yourColumnName],FIND("Serial",[yourColumnName])-6,4) )
Improve this answer
2
  • that is FANTASTIC... this is great,, i did have to change the end "-7,5))" instead of "-6,4))", sometimes they come in with 5 characters ie. 17261 or 07261, i wish i could add something to the logic where if the first character is '0' of 07261 then strip the '0' and leave 7261. THANK you so much Mike .. i have been banging my head on this all afternoon....
    – marco
    Commented Feb 18, 2019 at 2:32
  • one more,, i do like this one =0 + MID( [Subject], FIND("Serial", [Subject] ) - 8, 6 ),,,, but it adds in a comma ie. 7,261
    – marco
    Commented Feb 18, 2019 at 2:36
1

You can use this formula:

=IF(LEFT(MID([Title],FIND("Serial",Title)-7,5),1)="0",RIGHT(MID([Title],FIND("Serial",Title)-7,5),4),MID([Title],FIND("Serial",Title)-7,5))

Here is my test: enter image description here

Improve this answer
2
  • fricken amazing, works like a charm, thank SOO much man.
    – marco
    Commented Feb 18, 2019 at 16:57
  • Welcome. Please accept if it helped you.
    – Michael Han
    Commented Feb 19, 2019 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.