LinkBack URL
About LinkBacks
hi all,
please help me find the right formula for this concatenation.
here's my sample data;
sample.JPG
please take note on the "/" that inserted between the concatenation.
hi all,
please help me find the right formula for this concatenation.
here's my sample data;
sample.JPG
please take note on the "/" that inserted between the concatenation.
try
Please Login or Register to view this content.
Try:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
In A2 and drag down
Formula:Please Login or Register to view this content.
In B2 and drag down
Formula:Please Login or Register to view this content.
in C2 and drag down
Formula:Please Login or Register to view this content.
More than one way to skin this cat though so you may get a more dynamic or elegant solution elsewhere but this does the job
Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back
Please also mark your thread as solved once it has been.
D'oh, I've done the reverse! TMS has nailed this one
Mmm. Might end up with an extra slash on the end. You can remove that easily enough.
that's my main problem, what if i have thousands list, how do i delete all "/" in the end. sorry for trouble.Originally Posted by TMS
There would only be 1 extra slash at the end. Your data doesn't show thousands, it shows 3. The formula is not scalable to thousands. For that, you would need a VBA UDF.
One way ...
=MID(IF(A1="","","/"&A1)&IF(B1="","","/"&B1)&IF(C1="","","/"&C1)&IF(D1="","","/"&D1),2,10000)
TMS: i think OP means thousand of rows not thousands of columns
in which case
Formula:Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
sorry i mean rows. but thanks for the time.
this works pretty fine. thank you so muchTMS: i think OP means thousand of rows not thousands of columns
in which case
Formula:Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks