Trim Function

berg8630 

As variant

=MID(E2,1, MIN(FIND({0,1,2,3,4,5,6,7,8,9},E2 & "0123456789")) +2)

Perhaps as array formula (Ctrl+Shift+Enter) if not on 365 or 2021.

berg8630 Not sure what H2O had in mind with his/her/its AI reply but perhaps the picture below demonstrates what you asked for.

The formula in F2 is like this:

=MID(E2,1,2+MATCH(TRUE,(ISNUMBER(--(MID(E2,SEQUENCE(LEN(E2)),1)))),0))

Note that it requires Excle365 or 2021 due to the use of the SEQUENCE function.

berg8630 

The TRIM function in Excel removes all leading and trailing spaces from a text string. It does not remove any internal spaces.

The syntax for the TRIM function is:

=TRIM(text)

• text: The text string that you want to trim.

For example, if the cell A1 contains the text string " This is a text string ", then the formula =TRIM(A1) would return the text string "This is a text string".

The TRIM function is a very useful function for cleaning up text strings. It can be used to remove unwanted spaces from cell values, text boxes, and other objects.

Here are some examples of how to use the TRIM function:

• To remove leading and trailing spaces from a cell value, you would use the following formula:

=TRIM(A1)

• To remove leading and trailing spaces from a text box, you would use the following code:

Sub TrimTextbox()
Dim txtBox As Object
Set txtBox = ActiveSheet.TextBoxes("TextBox1")
txtBox.Text = TRIM(txtBox.Text)
End Sub

• To remove leading and trailing spaces from an object, you would use the following code:

Sub TrimObject()
Dim obj As Object
Set obj = ActiveSheet.Shapes("Shape1")
obj.Text = TRIM(obj.Text)
End Sub