0

Help! Trying to strip prices from a cell in Excel. Will always have either a '$' and/or a '.' in the amount. Problems arise since 1) there can be multiple currency formats, 2) there are other numbers in cell, 3) I don't want to strip out carriage return formatting

Example Cell Content: This should strip out $159.95 and 69.95

90,000 Mile
Intake Service $159.95
Air Filter 69.95
Rear Brake Pads (4MM)

Example Cell Content

This is an easy find/replace regular expression in Notepad++, but I'm not allowed to access this at work. Does Excel have something to find '$' and remove it plus numbers after it up until the next space or carriage return?

The closest I've found is a MID or TEXTAFTER function but it also takes out all of the data after the '$' or it removes all the carriage returns.

....................

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
ExcelNoob
  • 3
  • 2
  • This looks really messy you have multiple lines in a single cell. If you want the end results to be the same I recommend do it in python with regex. If you want to keep it in excel split up the lines in the cell to be multiple cells. – Shane S Aug 11 '23 at 00:40
  • Another option is to use Powerquery in excel here is an example https://stackoverflow.com/a/72210898/5091720 Part of the problem is that there maybe some cells with 5 lines of data and other cells with 2 lines of data. The excel formulas will not work in those enviroments. – Shane S Aug 11 '23 at 00:45

1 Answers1

2

If you have Microsoft 365:

=SUBSTITUTE(
    TEXTJOIN(
        " ",
        ,
        LET(
            a, SUBSTITUTE(A1, CHAR(10), "~!"),
            b, TEXTSPLIT(a, , {" ", "~"}),
            c, ISNUMBER(FIND({"$", "."}, b)),
            d, BYROW(c, LAMBDA(arr, OR(arr))),
            e, FILTER(b, NOT(d)),
            e
        )
    ),
    "!",
    CHAR(10)
)

enter image description here

Algorithm

  • Replace the line feed with ~!
    • Enables splitting on space and tilde, leaving the ! to replace the linefeeds when we rejoin the string
  • Filter the resultant array removing substrings that contain $ or .
    • Note that when find_text is an array, the result will be a multi-column array with one column for each element
      • We use the BYROW function to reduce this to a single column array for use in the FILTER function.
  • Rejoin with spaces
  • Then replace the ! with linefeeds

If you do not have Microsoft 365, this can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range or from within sheet
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • Read the code and comments. The algorithm is very similar to the formula above
let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    #"Remove Currency" = Table.TransformColumns(#"Changed Type",

//split each cell on the line feed
// then split each split on space
// remove items that contain $ or .
// reassemble
    {"Column1", (c)=>
       try
        let 
            lines = Text.Split(c,"#(lf)"),
            removeCurr = List.Transform(lines, (d)=>
                let 
                    words = Text.Split(d," "),
                    notCurr = List.Select(words, each not Text.Contains(_,"$") and not Text.Contains(_,"."))
                in 
                    notCurr)
        in 
            Text.Combine(List.Transform(removeCurr, each Text.Combine(_," ")), "#(lf)")

        otherwise null}
    )
in
    #"Remove Currency"
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Oh...this makes so much sense. Thanks so much, I've been banging my head against a wall for hours. – ExcelNoob Aug 11 '23 at 02:50
  • 1
    @ExcelNoob please ensure to accept the answer given by Ron Sir by ticking the checkmark beside the answer. It is a valuable solution, going forward it might help someone in future. – Mayukh Bhattacharya Aug 11 '23 at 06:29