Sunday , 23 September 2018
Home >> E >> Enterprise Applications >> Better charts. Smarter lookups. Your Excel spreadsheets made better.

Better charts. Smarter lookups. Your Excel spreadsheets made better.

Now for a tougher trick – pulling price information from the end of a cell that contains this string:

01/19 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 27.68

Purchases can range from single dollar amounts (four characters, with the decimal) up to the thousands (seven or potentially even eight characters), so you can’t just use the RIGHT function to pull the last four or five characters. But if you know the position of the final space in the string, Excel can deduce the number of characters in the price.

Use the LEN function to get the cell contents length in characters (49) and SUBSTITUTE to get the content length with all spaces removed (43), like this:

=LEN($A2)-LEN(SUBSTITUTE($A2,” “,””))

The result (6) is how many spaces in that cell. Using SUBSTITUTE again, replace that 6th and final space before the price with a unique character (I use the pipe “|” symbol), thusly: =SUBSTITUTE($A2,” “,”|”,6).

Now, count the number of characters until the pipe symbol, using the FIND function, like so: =FIND(“|”,$A4)

This formula returns 44. Subtract that from the total length of the cell contents (49) and you get 5, which is the number of characters in 27.68. The final formula to isolate the price value is:

=RIGHT($A4,(LEN($A4)-FIND(“|”,$A4)))

Now I can sort, filter and use conditional formatting to get a better handle on my purchase habits and patterns.

close
==[ Click Here 1X ] [ Close ]==