Related Posts
Vettel you beauty ❤️
Cloud surfing to Vegas.
Situation true for most of us now a days...
Additional Posts in Excel Genius
Visual Storyteller
Would anyone be willing to teach me vlookup?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
CONCAT(LEFT((TEXTSPLIT(yourtext,” “)))
OP this is 100% your solution.
How consistent is the text? I would use Concat nested functions. The first nested function would need to grab the first letter. I can’t think of what it would be off the top of my head. The second/third nested functions could be TextAfter and use a space (“ “) as the delimiter.
It's consistent. All in title case.
Concat(
Left(A1,1),
Iferror(mid(A1,search(“ “,A1),1),””)
Iferror(mid(A1,search(“ “,A1,(search(“ “,A1)+1)),1),””))
This takes the first letter, the letter after the first space and the letter after the second space. If you have more than three words you can add another part where you use the position of the second space+1 as the starting point for the search
I wrote this on my phone so there might be syntax errors
Not sure how to do it off the top of my head, but one algorithm could be to parse each cell searching for the first letter of each word, then concatenate them. Excel might not be the best tool to use. Maybe a regular expression method. I'm interested in a good way, too.
Create the acronym table then Vlookup the City cell and return acronym.
Also, you could consider VBA Find and Replace.
✅I agree and think this is neater.
It allows you to add in multiple long versions that convert to the same city, or to change the acronym.
Like changing ‘New York’ and ‘New York City’ to ‘NYC’.
It would also allow you to change some acronyms to be longer to avoid duplicates (otherwise Charlotte and Chicago will both return ‘C’).
First create a lookup table key for the acronyms.
So select all the data, copy as values into a new column, then go to the data and select remove duplicates (this will give you all the unique cities in your list). Then, in the column next to your unique values, type in the acronym you want each city to be represented by.
Next, in the column next to your original data use vlookup( the cell to the left, the unique data table, column 2, exact match)
Yea this does just that. No matter how you do it you have to write what the acronym is at least once. This is just done in a “look up table”
Here is my (sloppy) solution.
Assuming F7 contains your city, and assuming that you have three words maximum:
=iferror(MID($F$7,1,1),"")&
iferror(mid($F$7,FIND(" ",$F$7,1)+1,1),"")&
iferror(mid($F$7,FIND(" ",$F$7,FIND(" ",$F$7,1)+1)+1,1),"")
Where you just continue the FIND chain if you have more than three words
https://www.ablebits.com/office-addins-blog/excel-replace-substitute-functions/
dim szColValue as string
dim szColValueUpperCase as string
dim szColRange as string
dim szIndividualWords() as string
dim szAcronym as string
' CAPTURE THE COLUMN VALUE
szColRange = "A1"
szColValue = Range(szColRange).Value
szColValueUpperCase = UCase(szColValue)
' SPLIT THE UPPERCASE COLUMN VALUE BY THE SPACES
szIndividualWords = Split(szColValueUpperCase, " ")
' WALK THE FIRST LETTER OF EACH SPLIT INTO THE ACRONYM
szAcronym = ""
for (lIdx = LBound(szIndividualWords) to UBound(szIndividualWords)
szAcronym = szAcronym & left(szIndividualWords(lIdx),1)
next
My first step would be to just see what Flash Fill decides to do. Haha
Flash fill should work