How to replace one character in every row of a column

excel replace special characters
excel substitute multiple characters
excel replace character formula
sql replace part of string
find and replace in sql
update substring sql
excel replace character in column
excel replace character in cell formula

I have a column containing the following strings:

1997.2 
1998.3 
2000.1 
2003.3
2008.4 

I would like to replace every period . with the letter q.

How can I do this, either in R or Stata?


In R.

Consider your string is x <- c("1997.2", "1998.3", "2000.1", "2003.3","2008.4"), then you can use gsub to find and replace.

> gsub("\\.", "q", x)
[1] "1997q2" "1998q3" "2000q1" "2003q3" "2008q4"

Replace specific character from all rows in database column, instead of ° to all items of one column. The column is called Chemical and its in a table called ChemicalResistance . Does anybody have an idea how to change  Sometimes, you want to search and replace a substring with a new one in a column e.g., change a dead link to a new one, rename an obsolete product to the new name, etc. SQL provides a very helpful string function called REPLACE that allows you to replace all occurrences of a substring in a string with a new substring.


Another option is chartr in R

chartr(".", "q", x)
#[1] "1997q2" "1998q3" "2000q1" "2003q3" "2008q4"
data
x <- c("1997.2", "1998.3", "2000.1", "2003.3","2008.4")

Excel formula: Replace one character with another, To replace or substitute all occurrences of one character with another character, you can use the substitute function. In the example shown, the formula in C6 is: For example, to limit your search to a specific column, select that column first, and then open the Find and Replace dialog. Find cells with specific format in Excel To find cells with certain formatting, press the Ctrl + F shortcut to open the Find and Replace dialog, click Options , then click the Format… button in the upper right corner, and define your selections in Excel Find Format dialog box.


As @dash2 implies for R, you won't be much better off with such a string variable (not column) in Stata. You need a quarterly date variable which numerically is the number of quarters from the first quarter of 1960 and which is formatted as you please. Here I just use the default format for quarterly dates.

The essentials are all documented under help datetime:

 clear 
 input str6 yourproblem 
 1997.2 
 1998.3 
 2000.1 
 2003.3 
 2008.4
 end 

 gen yoursolution = yq(real(substr(yourprob, 1, 4)), real(substr(yourprob, -1, 1)))

 format yoursol %tq 

 list 

      +---------------------+
     | yourpr~m   yourso~n |
     |---------------------|
  1. |   1997.2     1997q2 |
  2. |   1998.3     1998q3 |
  3. |   2000.1     2000q1 |
  4. |   2003.3     2003q3 |
  5. |   2008.4     2008q4 |
     +---------------------+

This also works:

gen alsoworks = quarterly(subinstr(yourprob, ".", " ", .), "YQ")
format alsoworks %tq 

How to remove all instances of a character in Excel range?, Remove all instance of a character with Find and Replace Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows  In Excel, the Find and Replace function can quickly find a character and replace it with blank or other contents in a selection or the whole sheet. 1. Select the range you need and press Ctrl + H to enable the Find and Replace dialog, and type the character you want to remove into


In Stata you can replace using subinstr() function.

For your case, you can use the following:

replace X = subinstr(X, ".", "q", .)

Here, X is your string variable. Read about subinstr() in Stata's help by typing help subinstr() in Stata

How to replace nth/all occurrences of a character in string in Excel?, And what if replacing all occurrence of this characters in the string? and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge  BTW, if only a smaller part of the rows contains that character to replace, it could be a good idea to keep the transaction size down by adding a WHERE clause. Simply add the following at the end of the UPDATE statement:


Stata has datetime variables that are integers with a special format label attached so that they are readable (for example zero is 1960q1, one is 1960q2, and so on). This allows you to use time series commands, operators and calculate durations. I suggest you convert your string to the year-quarter one. If you still want to keep the data as a string, then the second approach should work:

clear
input str8 str_yq
1997.2
1998.3
2000.1
2003.3
2008.4
end
gen yq = quarterly(str_yq,"YQ")
format yq %tq
gen yq2 = subinstr(str_yq,".","q",1)

How to replace all occurrences of a character in a character column , How to replace all occurrences of a character in a column in a data Consider this vector: a<-c(1,2,3,NA,4,5,NA,NA) Write the function to impute . How to subset rows containing NA in a chosen column of a data frame? Column A contains numbers formatted using the General Format. Select the cells in range B4:B7 &right click on the mouse. Click on Format Cells orPress Ctrl+1 shortcut key on the keyboard to open the Format Cells dialog box if you do not want to use the right click menu.


Using Excel Find and Replace in the most efficient way, However, it can search only in one Type the characters (text or number) to search Replace all or selected occurrences, or export the found cells, rows or columns to. The lines in your expected output seem to end in two space characters and have fields separated by one tab and one space character. If that's indeed what you want, then you'd need: Or with sed: @123, like I said, in the OP's expected output, every line ends in two space characters.


REPLACE, REPLACEB functions, REPLACE replaces part of a text string, based on the number of characters you specify, with a different Otherwise, REPLACEB counts each character as 1. Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data. In Excel, you can find out all character “S” first, then replace the character with nothing. 1. Select a range or the whole worksheet as you need, and press Ctrl + H to display Find and Replace dialog, then type S into the Find what textbox, and leave nothing in Replace


SQL REPLACE Function: Search and Replace String in Database, REPLACE function to replace all occurrences of a substring with a new one. to replace the dash character by the dot character in the phone_number column Notice that the above UPDATE statement updates all rows in the employees  Replace multiple characters with single character in a column – Learn more on the SQLServerCentral forums