CODEWITHSUNDEEP
excelexcel-formulaformula
VIKRANT SINGH
VIKRANT SINGH

Reputation: 3

Split Text From the Last Space before the 35th position Character in Excel

e.g. Supplier Name - NANJING BANGSHING PRECISION MACHINERY TE

I want to break this Name from the last space before the 35th position.

In this Case 35th Position is - 'E' in MACHINERY, and I want to split it from the Space before it.

i.e. 

NANJING BANGSHING PRECISION - Cell A1
MACHINERY TE - Cell B2

Currently I am finding the position of 2nd last space(For any Supplier Name going above 35 Characters, as the names are not longer than 70 chars), in order to complete my task. However, it's not a good practice.

=FIND("^"|SUBSTITUTE(A1|" "|"^"|LEN(A1)-LEN(SUBSTITUTE(A1|" "|""))-1))

to find the position

NANJING BANGSHING PRECISION - Cell A1
MACHINERY TE - Cell B2

Upvotes: 0

Views: 402

Answers (1)

user11397601
user11397601

Reputation:

AGGREGATE can return the position of the last space before the 35th position. Use with REPLACE to remove unwanted characters.

'first part
=REPLACE(A2, AGGREGATE(14, 7, ROW($1:$35)/(MID(A2, ROW($1:$35), 1)=" "), 1), LEN(A2), "")
'second part
=REPLACE(A2, 1, AGGREGATE(14, 7, ROW($1:$36)/(MID(A2, ROW($1:$36), 1)=" "), 1), "")

It was a little unclear on what to do if the 35th position was actually a space. The above uses it as the stop. If this is not desired behavior, change the 35's to 34.

Upvotes: 2

Related Questions