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"
Best Answer

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
Answers

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
Categories
Check out the Formula Handbook template!