# 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"

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

• ✭✭✭✭✭✭

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?

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

• ✭✭✭✭✭✭

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)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!