Help with data

[stormshade]

I have a list of long names and each name has a 3 letter short name. I want to be able to paste a list of long names from a email or file to the spreadsheet and have it give me the short name for each one.

 

[hiker95]

stormshade,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[Ruddles]

Welcome to the forum!

What does your list of names and abbreviations look like and what does the worksheet look like after you've pasted the names from the email into it?

That will help us decide how they're to be handled.

 

[stormshade]

Using excel 2010

<TABLE style="WIDTH: 244pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=325><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 161pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=215>LONG NAME</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 83pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=110>SHORT NAME</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>Running Aces</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">ACE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Arapahoe Park</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">ARP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>Bangor Raceway</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">BNG</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Balmoral Park Race T</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">BMP</TD></TR></TBODY></TABLE>

this is what my data looks like. I would get a email from someone daily and I want to take the tracks they have and paste them into a spreadsheet and I would like it to tell me the short name for each one I pasted in. Hope this helps anymore info let me know.

 

[hiker95]

stormshade,

Can we have a screenshot of the worksheet that contains "I would get a email from someone daily and I want to take the tracks they have and paste them into a spreadsheet"?

And, what are your worksheet names?

 

[EYEMDABEST]

Do you have a complete list of the tracks? I would have them in a "short name" tab and then use the "vlookup" formula to pull the information from the "short name" tab. on the short name tab you only have to list the tracks once and add tracks as necessary then in the column next to the long name list the short name. On the main tab the formula in the "Short Name" column would look like this:

=VLOOKUP(A2,'Short Name'!$A$2:$B$25,2,FALSE)

When you add new long names just drag the formula down and it will populate the short name.

 

[stormshade]

My worksheet with all the long names and short names is called Plat_Tracks and the worksheet that is blank that I would paste into is called Plat.

A example of a email i would get looks like this

Tracks for Aug 3rd

Running Aces
Arapahoe Park
Bangor Raceway
Balmoral Park Race Track
Beulah Park
Beulah Park
Canterbury Park

 

[stormshade]

That worked great thanks. Is there anyway to make it pick up the short name if the long name is misspelled.

 

[hiker95]

stormshade,

Excel Workbook
	A	B
1	LONG NAME	SHORT NAME
2	Running Aces	ACE
3	Arapahoe Park	ARP
4	Bangor Raceway	BNG
5	Balmoral Park Race T	BMP
6
Plat_Tracks

Excel Workbook
	A	B
1	Tracks for Aug 3rd
2
3	Running Aces	ACE
4	Arapahoe Park	ARP
5	Bangor Raceway	BNG
6	Balmoral Park Race Track	not found
7	Beulah Park	not found
8	Beulah Park	not found
9	Canterbury Park	not found
10
Plat

The formula in cell B3 copied down (you will not have to adjust the bottom ranges in the formula, like you would have to using VLOOKUP):

=IF(ISNA(INDEX(Plat_Tracks!B:B,MATCH(TRIM(A3),Plat_Tracks!A:A,0),1)),"not found",INDEX(Plat_Tracks!B:B,MATCH(TRIM(A3),Plat_Tracks!A:A,0),1))



The YELLOW cell background indicates that the long name in worksheet Plat_Tracks should be corrected.


The above could be automated with a macro, based on the last row of data that you paste in worksheet Plat.

 

[stormshade]

Have a problem got new email and the send the time in front of long name. Anyway of ignoring the time?

<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=198><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7241" width=198><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 149pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17 width=198>1:00 Palm Beach (G)</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>4:55 Indiana Downs (H)</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>5:30 Presque Isle (T)</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>7:00 Mountaineer (T)</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>7:00 Northfield Park (H)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>7:25 Daytona (G)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=17>8:00 Running Aces (H)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>10:00 Tucson (G)</TD></TR></TBODY></TABLE>