Formula to Pull Data from Right of Cell Until Designated Space

You can use this Excel formula to pull data from the right side of a cell until the character specified in quotes before REPT appears in the referenced cell. So if you have data in A1 and enter the formula in B1 . .

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

. . . it will pull out the text until the first space appears moving from right to left.

Thanks to VoG for posting this formula here: http://www.mrexcel.com/forum/excel-questions/466176-extract-characters-right-till-space.html

Numbers in Excel Over 15 Digits

Be careful when entering digits in Excel more than 15 digits long. Unless a field is formatted for text, Excel will replace the digits past the 15th one you enter with zeroes. See confirmation from Microsoft on this point here.

This is the kind of thing which is very easy to miss if you're working with lots of data.

See this example, where I type in a 19 digits number . . .

. . . but Excel automatically truncates the last four digits.

Count how many ranges an value falls in

Here's an update to the tip of the night for September 8, 2016. Using the same formula you can count how many different ranges a value falls in.

As noted previously the SUMPRODUCT formula searches through the ranges in columns F and G and pulls the value listed in column H when it gets a hit. If it gets more than one hit it will add up the the values in column H. In this example we can see that that formula in B1 shows that the value in A1 falls within two ranges in column F and G.