BIG Cleaning Data

I have 5 columns of cleaning addresses formulas (one after one)
All formulas working good, but 5 steps of formulas is too much.
I ask ExcelForum experts to help me reducing the number of steps and check formulas for errors.
Formulaset must working on Excel 2019, without VBA, UDF, PQ, without helper columns,.. only regular or array formulas.
I know debugging is not an easy task, that's because I ask only experts.
I provide a screenshot and workbook with full data and comments.
Thank You.

Excelforum_Merge.png

I don't have 2019 (and hence some of the formulae used is "alien" to me). However given the multiple SUBSTITUTE statements used, I would be surprised if you get a shorter solution. Can you really combine formula 2 & 3 !.

The real solution is to solve the problem at source : why are the addresses so wrong?

If you don't mind to use UDFs. Please try

Please Login or Register  to view this content.

With this UDF, you may use together with PROPER() and TRIM()

Regards.

The OP requested a formula - and no helper columns! - not VBA, solution [although I would look at the VBA option]: however some companies do not allow VBA solution.

Hmmm, in that case maybe LAMBDA can help ?

something like....
=LAMBDA(Original,From,To,SUBSTITUTE(Orighnal,From,To))

I use Excel 2016, so not sure about them.

Regards.

That's a first, I've never hit the 8,192 character limit in a formula before! Looks like just hammering the formula together isn't an option then

Is PowerQuery an option? Some good Youtube tutorials available if you search for 'powerquery data cleansing'

In that case , could series of SUBSTITUTE help ?

Plese review in this file.
Note:
1 The substitute will do in order , so it will be effect if you change some of them.
2 The file are structured so you can add more 'substitute level' if need.



Regards.

For more your information, in the 1st level I've add Lower(), Clean(), so in the Eliminated data will be use only lower chars.

Please Login or Register  to view this content.

Regards.

In case you want only 1 column of answer, you may increase number of substitute and cover all of them with
Proper( Trim ( .... ) )

Note : Eliminate sheet is still require but all become 1 level.


Regards.

If you are really need only a formula, you may combind your 1st level formula with nested substitute as in my sample file.
Note: for excel 2016 only nested () can not over 64 levels, so my limit of substitute for the formula is 54.

Regards.

Note : you may remove some charactor in text string (of your formula) to reduce the amount of substitute.