Vlookup every letter in a text string.

Isaactd

New Member
Joined
Mar 3, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
Hi Guys,

When you have a chance this is the problem I'm trying to figure out.

I have a cell that contains text strings with different lengths.

I'd like to vlookup each of the single characters in this cell counting duplicates.

I'd like to find an exact match for each letter in the string matching letter, case and selected font from my table

Text= "Hello"

Font= beans


This is my lookup table: formula s in the 4th column

A bean uppercase

W bean uppercase

g bean lowercase

o bean lowercase

Finds a perfect match with "o" and returns 1.

If the looked up character cannot be found, in the example "H,e,l,l" cannot be found.


Then ask what is case of each letter? Then if lowercase search for letter "g" if uppercase ="w" now find exact match and add 1 to column D for every match you find if no match font show "error"


Here are some expected results of my formula text "Hello" matching "bean"

A bean uppercase=0

W bean uppercase=1

g bean lowercase =3

o bean lowercase =1

W apple uppercase =0

g apple lowercase =0


Any ideas to do this formula?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
There is no ordinary formula function that can get you font of a cell. Why does this need to be a formula?
 

Isaactd

New Member
Joined
Mar 3, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
Hi Glen,

The font is not important thats just part of my data set in column b.
 

Isaactd

New Member
Joined
Mar 3, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
There are 3 columns of variables
A=Letter
B=Font
C=Case
D= Where the formula is I'm wanting to figure out.
E= TEXT LOOKUP VALUE
D= FONT LOOKUP VALUE
 

Isaactd

New Member
Joined
Mar 3, 2021
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
Dataset currently just w & g but I will add other letters in time
 

Attachments

  • Screenshot_20210306-001044.jpg
    Screenshot_20210306-001044.jpg
    142.9 KB · Views: 11

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I really cannot grasp what you are aiming for. And, I'm guessing from the lack of other responses, that no-one else grasps it either.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,426
Messages
5,642,055
Members
417,252
Latest member
selbysam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top