When working with text data in Excel, you may sometimes need to add the same text to existing cells to make things clearer. For example, you might want to put some prefix at the beginning of each cell, insert a special symbol at the end, or place certain text before a formula. Continue reading
Comments page 2. Total comments: 93
Dear Team,
I want to add spaces like the expected results,
thanks a lot in advance:-
C2160feetRoad
C41OPP240B
C284981C12
G14sunderIndustrialstate
Their expected results should be as:-
c2 160 feet Road
c41 OPP 240 B
c2 84981 c12
G14 sunder industrial state
Hi! To make a formula and add spaces, you need to see some kind of regularity or pattern in your data. Unfortunately, I don't see that.
Thanks Sir,
possible to formula to add space ist after two character,then three character,then three character in a single cell please
Hi! To insert a character at a specific position, you can use the REPLACE function. For example, insert space:
=REPLACE(A1,3,0," ")
Thanks Sir,
This is only for ist three character, but i need a single formula to make space in a cell after every two or three spaces
e,g:C2160feetRoad should be as c2 160 feet Road.
Hi! To insert a space after every third character, split the text string with the MID function, then merge it with the TEXTJOIN function using a space as separator.
=TEXTJOIN(" ",TRUE,MID(A1,SEQUENCE(15,1,3,3),3))
Hello,
I'm attempting to extract the text from many cell notes. The form of the text in each cell note is:
ABC
DEF
GHI
I setup the following function:
Function getComment(incell As Range) as String
On Error Resume Next
getComment = incell.Comment.Text
End Function
----------------
When I call the function on a cell, the result is:
ABCDEFGHI
How to insert a delimiter such as 'space' or 'comma' in between each line so it looks like:
ABC,DEF,GHI
or
ABC DEF GHI
Thanks!
Hi
Grateful if you could please help. I have one cell AB and another cell C. How to add C between AB please?
Hi! If I understand your task correctly, try the following formula:
=REPLACE(A1,2,0,B1)
or
=LEFT(A1,1)&B1&RIGHT(A1,1)
where
A1="AB"
B1="C"
You can learn more about REPLACE function in Excel in this article on our blog.
I have data from a mainframe and the date fields do not have a / separator to separate the month/day/year. I can use a formula you provided. When I use =LEFT(E2, 4) &"/"& RIGHT(E2, LEN(E2) -4) this works perfectly to put a separator before the 4-character year, if the date is 8 characters in length. example: 11162002 will be 1116/2002 but if the date is 7 characters in length I get this - 6162002 will be 6162/002. If I move the left and right functions ( =RIGHT(E2, 4) &"/"& LEFT(E2, LEN(E2) -4) ) I get this result 2002/1116. This is very confusing to me. I need to count 4 characters starting from the right and insert a / and then count 7 characters from the right again to insert another / to all dates will look like this. 11/16/2002 for a 8 character date string or 6/16/2002 for a 7 character string but I am unable to find a solution. What would these 2 formulas look like. Please provide 1st formula to separate the year and 2nd formula to separate the month/day.
thank you in advance
Hi!
For your date 6162002 you can use the DATE formula and extract the necessary data using substring functions
=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
this works great, I've only tried a few so far. I would love to learn how this whole formula works - can you break this down bit-by-bit?
thanks in advance for your help
For more information on each of the LEFT, RIGHT and MID functions, read this article: Excel substring functions to extract text from cell.
This does not address inserting from the right.
I have a series of digits I wand to insert a decimal into from the right by 2 places. Every guide I find only addresses inserting from the left.
Hi!
Are the recommendations of the paragraph "How to add text to the end of cells in Excel" not suitable for you?
I might have overlooked something, and sorry if I did, but I'm not understanding how I would add a "." into an existing string, 2 places from the right.
562357 > 5623.57
Hi!
You need not add, but insert a symbol. Use the REPLACE function
=REPLACE(A1,LEN(A1)-1,0,".")
I hope it’ll be helpful.
Well I managed somehow.
Still don't see how the section you referred to is relevant to my question.
=RIGHT(REPLACE(M2,LEN(M2)-3+2,LEN(M2),""), 10) & "." & RIGHT(M2,2)
It is not helpful, in order to use this tutorial you have to already be an expert. You need to do the explanation for dummies as well.
Hello. How can I add brackets to all cells. For instance, the cell reads "$Stop1" and I want "{$Stop1}."
Hi!
Pay attention to the second and third paragraphs in the article above.
If the cell I'm drawing in with CONCAT is currency which ends in ".00" or, say, ".10", it truncates the zero so I get $2 or $2.2 without the remaining zeros. How do I force the 2 space decimal?
Hello!
To display the currency in the desired format, use the TEXT function.
For example,
=CONCATENATE("Your price is ",TEXT(A1, "$###,###.00"))
Function SplitWords(Txt As Range) As String
Dim Out$
If Len(Txt) = 0 Then Exit Function
Out = Mid(Txt, 1, 1)
For i = 2 To Len(Txt)
If Mid(Txt, i, 1) Like "[a-z]" And Mid(Txt, i + 1, 1) Like "[A-Z]" Then
Out = Out & Mid(Txt, i, 1) & " "
Else
Out = Out & Mid(Txt, i, 1)
End If
Next i
SplitWords = Out
End Function
HI! I need help.
I want a serial number entered on one sheet to automatically populate within a sentence on another sheet.
For instance:
A user enters the serial number on Sheet 1 in Cell B5 as 1234-Q.
I want Sheet 2 to have a sentence within a cell that says "the service for serial number '1234-Q' is complete". How can I do this? Paste special doesn't exactly work.
Appreciate any help, thanks!
Hello!
The answer to your question can be found in this article: Excel CONCATENATE function and "&" to combine strings and cells.
="the service for serial number "&Sheet1!B5&" is complete"
You can learn more about external references to another sheet in Excel in this article on our blog.
A B
1000403 DRUGS ALLOPURINOL
(ALLOPURINOL USP)
1000448 TRI ETHYL
ALUMINUM
1000465 TRI ETHYL
ALUMINUM
1000552 DRUGS: LOSARTAN
POTASSIUM USP
(LOSARTANPOTASSIUM)
1000916 COMBED
COTTON CORESPUN SEWING THREAD
70%POLYESTER FILAMENT YARN
42%
1001356 BOPP FILMS
BIAXIALLY ORIENTED POLYPROPYLENE FILM
in this database, I want to add texts spread over 2-3-4-n nos of Cells at Cl-B against individual entry(first column) in a single cell. Considering the database is huge and number of cell over which text is spread is not limited, use of concatenate may not be helpful.
Hi!
I don't really understand what you want to do. But for merging cells, you might find this article helpful: Merge and combine cells in Excel without losing data.
If this is not what you wanted, please describe the problem in more detail. Write an example of the result you want to get.
How can I add text to a formula like =SUM(224/12) so the result would be 18.6 days
Hi!
You can add text to a formula using the & operator. Read more in this article: CONCATENATE in Excel: combine text strings, cells and columns.
=SUM(224/12)&" days"
How do I add a comma and a space between characters? My cells return multiple characters in one cell (I want that) and I would like to add a comma and space between each character.
Thank you for your help!!
Elyse
Hello!
What formula do you use to get the characters in a cell? We need to change this formula. To write text with commas in another cell you can use the TEXTJOIN function
=TEXTJOIN(",",TRUE,MID(A1,ROW(A1:A50),1))
I want to add "Address" but can not put as =""Address"" since it does not take it as text. how I can add this ?
Hi!
If Excel does not treat your text as a formula, add an apostrophe ' before the text.
how do you enter name, and letter by letter it goes to each next Cell,
for example : JOHN RAMBO,
J O H N R A M B O in each cell
Hi!
After each letter, press the cursor arrow key to move to the next cell.
i need to copy data from other form and put in an excel sheet, which read each letter for each cell, kind of hard adding list of name to it by using arrow keys, as names from my country had kind of long names...
Hi!
Sorry, it's not quite clear what you are trying to achieve. Please describe your problem in more detail.
okay, the sheet than i had was make by the printing company and some how if edited it will be printed differently, im suppose to fill in the name from a physical form into the sheet, in which read each letter of the name in each cell, the problem was it become a hassle each time for me to enter the name for example :
JOHN DOE
i had to enter the name
J "ARROW KEY" O "ARROW KEY" H "ARROW KEY" N "ARROW KEY" *BLANK* "ARROW KEY" D "ARROW KEY" O "ARROW KEY" E
so in the sheet it will be [ J ][ O ][ H ][ N ][ ][ D ][ O ][ E ]
its okay to type in like this, but if you had 300 names with 21 letter, well... poop..
it will be helpful if you can solve this silly problem of mine... thank you
Hi!
Your problem can be solved with a VBA macro. But now I can only help you with Excel formulas.
Good day.
Please which formula in excel can ad space after 2nd upper case character.. e.g. "JohnDavis" to appear as "John Davis"
Thank you
Hello!
To insert a space before the second uppercase letter, use the formula:
=LEFT(A1,MATCH(TRUE,EXACT(MID(A1,ROW(2:50),1), UPPER(MID(A1,ROW(2:50),1))),0))&" "&MID(A1,MATCH(TRUE,EXACT(MID(A1,ROW(2:50),1), UPPER(MID(A1,ROW(2:50),1))),0)+1,50)
I hope it’ll be helpful.
Hi! can ask if this formula is possible to this
Macaraeg,ChristopherLabanaBautista
thank you
Hello!
To split sticky text and insert spaces into it, you need to use VBA. You can try to apply a user defined function
Function SplitWords(Txt As Range) As String
Dim Out$
If Len(Txt) = 0 Then Exit Function
Out = Mid(Txt, 1, 1)
For i = 2 To Len(Txt)
If Mid(Txt, i, 1) Like "[a-z]" And Mid(Txt, i + 1, 1) Like "[A-Z]" Then
Out = Out & Mid(Txt, i, 1) & " "
Else
Out = Out & Mid(Txt, i, 1)
End If
Next i
SplitWords = Out
End Function
To insert a space after a comma, use the SUBSTITUTE function.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.