Getting characters from before and after a text

Hi,

I have this value in a cell. I need some help to create a formula that can find the last '$' sign and get maximum of 4 number after the '$' sign and 6 numbers before the '$' sign from the data below.

"FB12345 $12345 ABCD 12345 SC

FB12346 $1234 ABCD 12345 SC

FB12347 $123 ABCD 12345 SC

FB12348 $12 ABCD 12345 SC"

The result I was expecting is "12348 $12"

Find more posts tagged with

Accepted answers

Zisun Mahmud Zihad

Yes, It has to be the number after FB which is 5 character long and the number after $ sign is maximum 3 character long.

By the way, I solved the problem myself. I hope this help anyone else whoever needs.

=MID(RIGHT([Primary Column]@row, 33), FIND("$", RIGHT([Primary Column]@row, 33)) - 6, 5)
 + " " + 
 MID(RIGHT([Primary Column]@row, 33), FIND("$", RIGHT([Primary Column]@row, 33)), 4))

In the formula, 33 is just a number I choose, so that the formula takes the last 33 characters which has only one $ sign. Then the formula will find the position of the $ sign and run though MID() formula to find the values.

Thank you @Paul Newcome

All comments

Paul Newcome

Did you want up to 4 characters after the $ or up to the space after the $ or a set 4 characters?

Will it always be 2 characters before the start of the string you want to pull?

Zisun Mahmud Zihad

Yes, It has to be the number after FB which is 5 character long and the number after $ sign is maximum 3 character long.

By the way, I solved the problem myself. I hope this help anyone else whoever needs.

=MID(RIGHT([Primary Column]@row, 33), FIND("$", RIGHT([Primary Column]@row, 33)) - 6, 5)
 + " " + 
 MID(RIGHT([Primary Column]@row, 33), FIND("$", RIGHT([Primary Column]@row, 33)), 4))

Thank you @Paul Newcome

Paul Newcome

This will also work. If it is always starting on the 3rd character, then we can just use 3 as the starting point of the MID. Then we locate the space that comes after the $, subtract 3 from that (the space and the "FB"), and use that number as our number of characters to output.

=MID([Primary Column]@row, 3, FIND(" ", [Primary Column]@row, FIND("$", [Primary Column]@row)) - 3)

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.