How To Add A Letter To Every Cell In A Column That Contains A Letter Or Num?

[vcaruso]

I have a list of Lat/Lon coordinates on an Excel sheet that I want to import into my GPS/Plotter on my boat. However, I want to differentiate the newly imported coordinates that I am importing by prefacing them with the letter "L" or a special character. So the coordinate names are in one column of the sheet called "name" for the coordinate names. Within those cells are the names of 1200 coordinate names but I don't want them to mix in with my existing coordinates hence the need to differentiate them with a letter or special character. Any help solving this is greatly appreciated as I am a wreck when it comes to Excel. I can take you out into the Gulf Of Mexico and promise you will come home with dinner though.

Thank you,
Vince

 

[Peter_SSs]

Welcome to the MrExcel board!

Generally better to give your sample data (& expected results) in a way that helpers can easily copy to test with. I suggest that you investigate XL2BB

I am not entirely sure I have understood what you want so definitely test this with a copy of your workbook.

Sub Add_Prefix()
  Const myPrefix As String = "L"  '<- Change if you want
  
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate("""" & myPrefix & """&" & .Address)
  End With
End Sub

 

[alansidman]

An alternative solution is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"x", type text}, {"Y", type text}, {"name", type text}, {"cmt", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"name", each "L" & _, type text}})
in
    #"Added Prefix"

 

[vcaruso]

Thank you both. I will try the suggested solutions. Sorry for the delayed reply, I had some health problems that kept me away from access to a computer.

 

[vcaruso]

Unfortunately, both methods are above my skill level in Excel and I have no programming skills at all.

I attempted both solutions, but no success. I may have chosen a forum that is above my experience level as none of the suggested code appeared intuitive to me. I am truly a novice with Excel. It is very kind of you both to help out and I appreciate that very much. I hope the uploaded data helps, I impressed myself that I got the mini-sheet macro to work.

I am wanting to modify the "Name" column to preface it with a special letter or character. In my original post, I used the letter "L" as an example.

Thank you and best regards,
Vince

sample GPS numbers.xlsx
	A	B	C	D
1	X	Y	Name	Comment
2	-83.1279	27.94673	52 WRECK	OLD WRECK REMAINS WI
3	-82.8199	27.44388	7MILE14	110 FT BARGE WITH ST
4	-82.93	28.50833	BRIDGE	SKYWAY BRIDGE PARTS
5	-83.1267	29.00528	CHORSE	SEAHORSE REEF AREA
6	-82.8928	28.01432	CLEARWATER	VARIOUS REEF MATERIA
Sheet1

 

[Peter_SSs]

methods are above my skill level in Excel

To implement my suggestion

1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code from post #2 into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the Add_Prefix macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

If you still have trouble or want a more manual approach you can do something like this.
Use a vacant column to the right to enter the formula shown and copy that down to the bottom of your data.
Then select the data in that new column (F2:F10 for me) and Copy
Select C2 - Right click - Select Paste Special.. - Values - OK
Then delete the formulas from column F

vcaruso.xlsm
	C	D	E	F
1	name
2	45 WRECK			L45 WRECK
3	52 WRECK			L52 WRECK
4	Other 1			LOther 1
5	Other 2			LOther 2
6	Other 3			LOther 3
7	Other 4			LOther 4
8	Other 5			LOther 5
9	Other 6			LOther 6
10	Other 7			LOther 7
11
Sheet1

Cell Formulas
Range	Formula
F2:F10	F2	="L"&C2

 

[vcaruso]

Well how about that. Your magic or should I say logic worked very well. Very thankful for your continued help & patience with me. Glad to know this resource exists. Hopefully I can return a favor some day. If you every need advice on offshore fishing in Florida, hit me up.

Thank you,
Vince Caruso
Palm Harbor, FL

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, I removed your link and picture. The link might be construed as trying to advertise through the forum and in any case it did not work for me. But thanks for the offer - though you are a long way from Australia!

 

[alansidman]

To use Power Query here is some information

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[vcaruso]

You're welcome. Thanks for the follow-up.

BTW, I removed your link and picture. The link might be construed as trying to advertise through the forum and in any case it did not work for me. But thanks for the offer - though you are a long way from Australia!

Definitely not advertising, just trying to show you what it is I do. Thanks for taking care of the post.