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

  • 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 Feb 18 at 2:07
  • its actually from a email subject line.. – marco Feb 18 at 2:37
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) )
  • 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 Feb 18 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 Feb 18 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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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