Solution 1 gets my vote - I'm a fan of Notepad++ but if you do decide to go down the CSV suggestion in Solution 2 here is a far quicker alternative and this also fills in some of the gaps from Solution 2
* Insert a new column A either by right-click on the A, select Insert from the context menu or keyboard shortcut Ctrl and Plus symbol
* Type formula
= CONCAT("Blah blah ", B1)
in what is now cell A1 and either double-click the (rather tiny) square in the bottom of cell A1, or if you want to get cramp, drag it down your 100,000 rows.
* Highlight the whole of column A, right-click Copy (or keyboard Ctrl and C), then Right-Click Paste, Special, Values. This is only necessary to stop you losing the data in the next step
* Delete Column B (highlight column and right-click Delete or keyboard Ctrl and Minus)
* Save
No need for an extra row nor an extra sheet
Things to be cautious of
- If you have any formatting on things like dates with preceding zeroes or ID strings with preceding zeroes then those zeroes will be lost (and it is a real PITA to get them back/not lose them in the first place)
- watch out for spurious extra lines being added at the end (or columns to the right)
- I have sometimes ended up with UTF-8-
BOM encoding if I use Save As and don't choose the "right" CSV option
If you are going to utilise Excel then I would suggest using Power Query within Excel to read the existing CSV, retain any formatting and to add the new computed column.
E.g. the steps in Power Query would be
1. Source
2. Promoted Headers
3. Change Type <- delete this, this is what could lose you the formatting on preceding zeroes
4. Check any types you might really want to change for yourself
5. Add your custom column (note use "Blah blah " & B1 rather than "Blah blah" & " " & B1
6. Put the custom column at the front
7. Remove the original Column A
Here's the M-code I used
let
Source = Csv.Document(File.Contents("C:\Dev\Demo.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each "Blah blah " & [#" Column2"]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Column1", "Custom", " Column2", " Column3", "date", ""}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1"})
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Column1"}})
in
#"Renamed Columns"
And the before and after CSVs
Column1, Column2, Column3,date,id
a,a,A,01/01/2022,00001
b,b,B,02/02/2022,00002
c,c,C,03/03/2022,00003
Column1, Column2, Column3,date,id
Blah blah a,a,A,01/01/2022,00001
Blah blah b,b,B,02/02/2022,00002
Blah blah c,c,C,03/03/2022,00003