Log In
Register
Home
Active Member
April 22, 2020
Offline
Question 1
Column 1
NO.1 JALAN IMPIAN TIGA TAMAN IMPIAN
Column 2
81500 PEKAN NANAS
Column 3
JOHOR
Column 4
81500
Column 5
JALAN IMPIAN
Correct one
NO.1 JALAN IMPIAN TIGA TAMAN IMPIAN 81500 PEKAN NANAS JOHOR
how to set the formula following by queue by wording. like No. (1st queue) Jalan (2nd queue) Taman (3rd queue) 81500 PEKAN NANAS JOHOR (4th queue)
Question 2
Column 1
LOT 1271
Column 2
Column 3
LORONG 2 KIRI
Column 4
JALAN GONG PASIR
Column 5
Column 6
23000 DUNGUN
Column 7
TERENGGANU
Column 8
JALAN GONG PASIR
this is Correct one
LOT 1271 LORONG 2 KIRI JALAN GONG PASIR 23000 DUNGUN TERENGGANU
Trusted Members
December 20, 2019
Offline
Hi
It is much easier to work on a formula if you attach a workbook so we can see exactly how the data is setup and we don’t need to spend time creating the data.
That said I think the below would work but it will need testing
column I - put a single space
column J - concatenate all the columns with a space in between =A1&I1&B1&I1&C1&I1&D1&I1&E1&I1&F1&I1&G1&I1&H1
column K - Substitute the second version of columns d through to h, so each substitute works on the previous one =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J1,D1,"",2),E1,"",2),F1,"",2),G1,"",2),H1,"",2)
Purfleet
Active Member
April 22, 2020
Offline
hi Purfleet, the atachment was missing. still dont understand. May I know what is the 2 meaning?
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J1,D1,"",2),E1,"",2),F1,"",2),G1,"",2),H1,"",2)
thanks
COLUMN
B C D E F G H I J
column J - concatenate all the columns
B & C IS NAME AFTER IT IS BELONG FOR ADDRESS
1. HOW TO REMOVE THE G & I DUPLICATE?
2. SOME WORDINGS FROM COLUMN B AND C MAYBE NOT IN G & I ONLY WILL BETWEEN IN COLUMN D - I .
DUE TO THE DATA 90% ADDRESS WILL DUPLICATE WORDINGS FROM COLUMN B AND C.
October 5, 2010
Offline
Wayne,
Please attach your workbook. I emailed you directly yesterday asking you to do this.
The 2 in SUBSTITUTE indicates which instance of the text to replace. You can read about this here
Using SUBSTITUTE to Find and Count Text
Regards
Phil
1 Guest(s)
