• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split string into multiple columns based on integers and uppercase

saidhruv

Member
Dear All - can you please help in splitting string into 3 different columns. I have shared the pattern. First output to have integers. Third output to have words with upper case and everything after that. The second output to have the middle characters. Please check excel. I am unable to write a formula.

thanks in advance!
 

Attachments

  • 3000.xlsx
    8.6 KB · Views: 5
Dear Bosco - i apologize for not sending all flavors of data in the first instance. Your formula is perfect for column 3, however due to variety of data it has to be updated for 1st and 2nd output. Can you please help me out here. Have shared your excel and updated the different style of data.
warm regards ..
 

Attachments

  • SplitString.xlsx
    11.5 KB · Views: 1
Dear Bosco - i apologize for not sending all flavors of data in the first instance. Your formula is perfect for column 3, however due to variety of data it has to be updated for 1st and 2nd output. Can you please help me out here. Have shared your excel and updated the different style of data.
warm regards ..

Please see revised formulae and attachment.

Regards
 

Attachments

  • SplitString(1).xlsx
    12.1 KB · Views: 15
@bosco_yip , the formula is quite interesting. How does the "-" symbol make the formula get the value?
Try see this example :

1] In A3 : 769404902MinnetonkaPETROLIANCE LLC824 Bittersweet Rd.

2] B3, formula :

=-LOOKUP(1,-LEFT(A3,ROW($1:$16)))

3] Formula explanation

=-LOOKUP(1,-LEFT(A3,ROW($1:$16)))

The 2nd "-" convert all Lookup Range numeric value into negative values and text into #VALUE

>>

=-LOOKUP(1,{-7;-76;-769;-7694;-76940;-769404;-7694049;-76940490;-769404902;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})

Since the Lookup criteria 1 is the BigNum, and all Lookup range values are negative value,
The Lookup formula ignore errors and return the last value.

=--769404902

The 1st "-" coerce the 2nd "-" into positive value


>>

769404902

The desired result

Regards
Bosco
 
Back
Top