Jump to content


Photo

Cells containing Numbers and Letters


  • Please log in to reply
1 reply to this topic

#1 Marla

Marla

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 15 May 2017 - 04:04 PM

I need help with a spreadsheet that has numbers and two letters at the end of the numbers.  I want the number to increase by one each time and I want the letters to stay the same.  Example - 9910TB starting would be on row A1, Row A2 should be 9911TB, Row A3 should be 9912TB. 

 

Please tell me the way to accomplish this.

Thanks,



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 15 May 2017 - 09:28 PM

Hi Marla,

 

The easiest way to do this would be to use the Flash Fill feature, as shown in the video lesson: Essential Skills Lesson 2-18 Use automatic Flash Fill to split delimited text.

 

Flash Fill was introduced in Excel 2013 so you will only be able to use it if you have Excel 2013 or a newer version (such as Excel 2016).

 

If you're not able to use Flash Fill, you can achieve the same thing by creating a column of ascending numbers and then using the concatenation operator (&) to append the letters "TB" to the number.

 

You can see how to quickly create ascending numbers in the video lesson: Essential Skills Lesson 2-14 Use AutoFill for text and numeric series.

For more on the concatenation operator, see the video tutorial: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

I have attached an example workbook showing this in action:

 

Attached File  TBCodes.xlsx   9.78KB   32 downloads

TBCodes.png

 

I hope this is the answer you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users