Forumula help to extra characters from a cell

WWARRIOR2018

Morning

I am hoping someone has an idea how I can achieve the following

I have a barcode which is formatted like this when scanned into Smartsheet

(01)00843997014755(11)240401(21)700040293

What I need for example is when the barcode is scanned into lets say cell A, then automatically cell B,C and D will extra data from this cell

Cell B = Everything between (01) and (11)

Cell C = Everything between (11) and (21 )

Cell D = Everything after (21)

Attachment shows the idea final result which I am looking for, I am hoping there will be a formula I can put in each cell to grab this info as I am scanning it

Thanks every for all your help, great community which I have learnt alot from

Find more posts tagged with

Formulas

Accepted answers

kowal

hey,

so if you have any no digits in the string like () you need to remove the value formula and use only

=MID(EANTESTING@row, 14, 7)

if your result from previous formula is (01)00843997 and you always have the same amount of digits you can use on it =right(new@row, 10) so it will give you: 00843997

All comments

kowal

hello @WWARRIOR2018

you can use formulas like:

column B: =VALUE(LEFT(A@row, 12))

column C: =VALUE(MID(A@row, 14, 7))

columnd D: =VALUE(RIGHT(A@row, 9))

let me know if it helps.

Paul Newcome

Will the number of characters in between always be the same?

WWARRIOR2018

https://community.smartsheet.com/discussion/comment/462370#Comment_462370

Cheers for replying so quick, I tryed all 3 it but got #invalidvalue back when I tryed

The column is now called EANTESTING so this was what I was using

=VALUE(LEFT(EANTESTING@row, 12))

WWARRIOR2018

yes

kowal

hi @WWARRIOR2018,

sorry I did not notice you have brackets there so value is only for pure digits.

=LEFT(EANTESTING@row, 12)

this shall help

WWARRIOR2018

https://community.smartsheet.com/discussion/comment/462471#Comment_462471

@kowal Thanks for all your help

With the first one =LEFT(EANTESTING@row, 12) when I run this the result is (01)00843997 , can you think of a way we could ignore the (01) so it just returns the number 0084399755

Then when I tryed the other formula's, I was still getting invalid value, the formula looks like this =VALUE(MID(EANTESTING@row, 14, 7)) , again with this one I want it to try and ignore the (11) and just return back 240401

Thankyou again for all your help

kowal

hey,

so if you have any no digits in the string like () you need to remove the value formula and use only

=MID(EANTESTING@row, 14, 7)

if your result from previous formula is (01)00843997 and you always have the same amount of digits you can use on it =right(new@row, 10) so it will give you: 00843997

WWARRIOR2018

Kowal, that all worked spot on thankyou again for all your help

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.