I have this words on A1
BANALER
CARINAL
CLARAIN
CRANIAL
CANULAR
LACUNAR
ADRENAL
LANIARD
NADIRAL
ALNAGER
I have this on C1
LNR
LNR
LNR
LNR
LNR
LNR
LNR
LNR
LNR
LNR
I have this on b1 through formula
AAE
AAI
AAI
AAI
AAU
AAU
AAE
AAI
AAI
AAE
I want this on d1
B
C
C
C
D
D
D
D
D
G
Last edited by makinmomb; 11-18-2016 at 07:00 AM.
What's your question?
You haven't supplied any information. I can see how your columns B & C have the letters in Column A but column D does not make any sense.
Can you explain the logic in this? You say you have a formula, what is it?
Putting it simpler that data on a and b and c , need formula on d to give results as attached M M B C
AAA AMANDLA DLN m
AAA MANDALA DLN m
AAI BALADIN DLN b
AAE CANALED DLN c
Use this in D1, copied down...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,MID(B1,1,1),""),MID(B1,2,1),""),MID(B1,3,1),""),MID(C1,1,1),""),MID(C1,2,1),""),MID(C1,3,1),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Not works give results
Blank
Blank
I
E
Aaa amandla dln m
aaa mandala dln m
aai baladin dln b
aae canaled dln c
MMBC on d1 d2 d3 d4 through formula
You must be doing something wrong...
Grrr. You changed the question... My first works with Post 1. This with Post 3
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,MID(A1,1,1),""),MID(A1,2,1),""),MID(A1,3,1),""),MID(C1,1,1),""),MID(C1,2,1),""),MID(C1,3,1),"")
Thank you very much Glenn Kennedy this puts me into gears of playing better scrabbling
You're welcome!!
The formula is not able to handle all words it leaves blanks in some ?
ALIDADS DLS AAI
GELADAS DLS AAE G
ASLAKED DLS AAE K
SALADES DLS AAE
SALSAED DLS AAE
What result do you expect where there are blanks as all letters are matched?
Last edited by JohnTopley; 11-18-2016 at 11:18 AM.
Alidads dls aai d
geladas dls aae g
aslaked dls aae k
salades dls aae s
salsaed dls aae s
Perhaps you should explain why you expect the results shown in your last posting.
Using Glenn's formula:
A
g
k
blank
blank
so it differentiates between Upper and lower case (required ?)
... Counts the number of a given character and outputs the balance ?
Alidads dls aai d
so 2 a, 1 l, 1 i, 2 d compared to 2 a, 1 d, 1 s,1 a leaving a "balance" of 1 d
????
all letters are capitals
ALIDADS DLS AAI D
GELADAS DLS AAE G
ASLAKED DLS AAE K
SALADES DLS AAE S
SALSAED DLS AAE S
Minus the 6 letters on B and C against word on A to give left out character
Ok...
=substitute(substitute(substitute(substitute(substitute(substitute(a1,mid(b1,1,1),"",1),mid(b1,2,1),"",1),mid(b1,3,1),"",1),mid(c1,1,1),"",1),mid(c1,2,1),"",1),mid(c1,3,1),"",1)
Thats it glenn , your first formula worked too , it was only failing on letters where there was repeat
Yes, I realise that now!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks