truncate?

[chrisz1059]

I have a string in a cell where the length changes but I always want to truncate after the last number reading from left to right.

I'm not sure if truncate is the correct function to use, or how to do it.

Thanks

[6StringJazzer]

Excel 365 does not have a function called TRUNCATE. It has TRUNC, which removes the decimal portion of a number.

To get a useful answer to this question you will to explain what kind of data is in the cell and what "truncate after the last number" means. Ideally you would attach a workbook showing before-and-after examples.

[chrisz1059]

Thanks for letting me know. Here's are 2 examples of the data I have, raw data cell:

CALL ABC 07/07/20 185.50 ABC CORP WE ACTED AS AGENT
CALL DEVO 12/12/20 17 DEVON INC CALL US FOR PRICE

What I need:

CALL ABC 07/07/20 185.50
CALL DEVO 12/12/20 17

Thanks

[Rick Rothstein]

I don't have Excel 365 so I don't know if that version allows for a simpler formula or not, but here is an array-entered** formula that appears to work...

=LEFT(A1,MAX(IF(ISNUMBER(-MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

NOTE: I think you can normally-enter this formula in Excel 365 and it should still work.

[XOR LX]

Hi,

Rick's formula can be abbreviated to:

=LEFT(A1,MATCH(1,-MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))

Or, 365:

=LEFT(A1,MATCH(1,-MID(A1,SEQUENCE(LEN(A1)),1)))

Regards

[chrisz1059]

Thanks to all

I have 365 and XOR LX's abbreviated function worked great. I'm going to save it in a custom workbook so I have the function for future use.

These are the steps I found when I did a search, any comments or suggestions:?

After you have created the functions you need, click File > Save As.

In Excel 2007, click the Microsoft Office Button, and click Save As

In the Save As dialog box, open the Save As Type drop-down list, and select Excel Add-In. Save the workbook under a recognizable name, such as MyFunctions, in the AddIns folder. The Save As dialog box will propose that folder, so all you need to do is accept the default location.

After you have saved the workbook, click File > Excel Options.

In Excel 2007, click the Microsoft Office Button, and click Excel Options.

In the Excel Options dialog box, click the Add-Ins category.

In the Manage drop-down list, select Excel Add-Ins. Then click the Go button.

In the Add-Ins dialog box, select the check box beside the name you used to save your workbook, as shown below.

[Rick Rothstein]

@XOR LX,

I don't think I have seen that construction for the MATCH function (match plus 1 in a resulting array of negative numbers and errors) before... I like it! However, I would point out that if the OP has blank cells within his data or if he wants to copy the formula down past the end of his data in anticipation of future additions to the data, you would need to add a trap those blank cells as your formula will generate a #N/A error for them whereas my formula natively outputs the empty text string ("") for blank cells. With that said, if the OP has a sense of some maximum length for the resulting output from the formula, say 99 characters maximum, then your match formula can be shortened to this (again possibly requiring a blank cell trap if the OP's data or usage so needs)...

=LEFT(A1,MATCH(1,-MID(A1,ROW($1:$99),1)))