Replace Some Spaces & Append Text
Aug 30, 2007
I need help looking at text in a cell that has [] around it such as [big red trucks] and copy that text to the end of the cell and replace the "space' between the words and add '+' signs so the result looks like [big red trucks] [big+red+trucks].
example:
This is what the cell looks like before
[big red trucks] cost 5000 in store
This is what the cell needs to look like after
[big red trucks] cost 5000 in store[big+reg+trucks]
there may be additional text after the ']', I need the phrase with the + signs copied to the end of the cell
View 3 Replies
ADVERTISEMENT
Apr 8, 2008
I want to append / replace data in cells depending on the initial data.
A cell has the value 3AB00456 XYZ
I want to append the value with My
I want to check to see if the 4th & 5th characters = 00
If YES, I want to append the value with My and replace the rest with just the the 1st 3 characters So the final value would be My3AB
If NO, (the cell has the value 3AB02456 XYZ) I want to replace with a hyphen after the 3rd character and then the rest of the cell value. So the final value would be My3AB-02456 XYZ
I have several worksheets each with a column of about 2000 cells. I want to replace the data in the cells and not create another row with the result.
View 9 Replies
View Related
Dec 10, 2011
A range of cells contain characters that i want to replace with spaces. The problem is that the length of the string differs and also the characters differ.
For example, below a list of cell values:
GR
GRKR
GRKRKR
GRKRK1
GRON
GRONBL
As a result for the first value i want 2 spaces, for the second one 4 spaces etc.
Is it possible to do this with a formula? I don't want to use VBA for this if possible...
View 5 Replies
View Related
Jul 18, 2013
I am trying to replace all spaces in a string with an underscore.
I'll be applying a loop to cycle through the whole column in that file. Should be pretty easy..
View 4 Replies
View Related
Nov 19, 2005
I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z8>0.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than a
zero because it alters future calculations.
View 9 Replies
View Related
Mar 7, 2014
I've been using =TRIM for a while... but just tried FIND "space bar" REPLACE "nothing" and it works fine and takes about 1/10 the time.
View 6 Replies
View Related
Apr 1, 2008
I've got a sheet in which I want a drop down box, to ADD the value* to a cell, not overwriting its current value!
*The name of the selected option in the drop down box, the names are located in Map3!A1-n, I set the drop down box to display the related number in a cell next to it.
The cell would contain some text, and by selecting something in the drop down box, it would add the name of that option to the already existing value in the cell.
So if at first the cell's value is
Hi! I 'm Mark,
and you select the following option from the dropdown box
I 'm from Holland!
the cell would end with the value
Hi! I 'm Mark, I'm from Holland!
This would probably work with a macro, already made a start with it but I couldn't get it to ADD the value instead of overwriting it.
View 9 Replies
View Related
Jan 14, 2009
I am having problems adding data to exisiting text in each row of column E. The following macro is replacing the current text in each row with "V-1954". I need it to add "V-1954" to the begining of the text in each row of column E not over write it.
Range("D2").Select
Do Until IsEmpty(ActiveCell.Value)
'Fill in Column E
ActiveCell.Offset(0, 1).Value = "V-1954"
'Move down one cell
ActiveCell.Offset(1, 0).Select
Loop
View 9 Replies
View Related
Apr 19, 2007
I would like to append data from Columns A-F to a text file. The key thing is that the data needs to be tab separated just as it would be if I simply highlighted it and copied it and the pasted it into the text file.
View 5 Replies
View Related
Feb 2, 2008
I have a database with Column "A" that looks like this:
"A"
PKJHB
PLKFSDA
KJGFSA
LJKDAS
GKKA
I want a code that would add ".pv" to every cell in "A" Column so it'll look like:
"A"
PKJHB.pv
PLKFSDA.pv
KJGFSA.pv
LJKDAS.pv
GKKA.pv
View 2 Replies
View Related
Feb 5, 2014
[Code] .....
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
View 6 Replies
View Related
Jan 16, 2010
I have a list of number in a column that there is over 22,000 is there a away I can add ".jpg" ad the end of each number?
View 10 Replies
View Related
Jul 10, 2009
I am looking for a macro to convert a 6 digital serial number into an 8 digit serial number by adding "00" to the front. ie. The cell may say 123456, but I need to change it to display 00123456.
Is there a simple way to do this?
View 6 Replies
View Related
Nov 28, 2013
I have a simple code that should insert the text at the beginning of the text file (the text file already has some text in it). When i use the following code, it just appends the line to the end of the text. I need it to append it to the start of the text file.
Code:
Private Sub CommandButton1_Click()
Dim file As String
lastrow = Range("D65536").End(xlUp).Row - 4
file = "C:Users11126923Desktop est.txt"
Open file For Append As #1
f = "hi test3"
Print #1, f
Close
End Sub
View 2 Replies
View Related
Oct 26, 2006
I want to open multiple .csv files from a single directory and append them to one workbook. The following code partly works, but appends only the first line from each file.
Sub GetFiles()
Dim w As Worksheet, fn As String, k As Long
Application. ScreenUpdating = False
Set w = ActiveSheet
k = Cells(65536, 1).End(xlUp).Row
If Not IsEmpty(Cells(k, 1)) Then k = k + 1
fn = Dir("*.csv")
While fn <> ""
Workbooks.OpenText Filename:=fn, Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
Rows(1).Copy w.Rows(k)
k = k + 1
ActiveWorkbook.Close False
fn = Dir
Wend
Application.CutCopyMode = False
End Sub
View 3 Replies
View Related
Aug 11, 2007
I write a daily status report that adds my daily comment to a cell with previous text in it. I then paste it in three other cells. This process is slow and tedious since the text in the cell is now becoming extremely long due to organizational and managerial restraints of the existing format. I use cut and paste and manual enter, a alt + enter, to space new comment. I would like to be able to enter the text in a cell and have it update the comment cell with the text in it and to update the text box. I have reviewed the forum and have yet to find the answer and use of how else to pose the questions.
View 5 Replies
View Related
Oct 15, 2007
I have a filename written in cell A1- eg dog.jpg - and I want to insert a set character string just before the .jpg part. ie so it becomes dog_test.jpg in B1. How would you guys do it? I've been thinking of really long-winded methods that'd use several cells, but I reckon there might be a cleverer way!
View 5 Replies
View Related
Jul 14, 2009
I have data that covers multiple rows that I need to merge into one row.
Header:| InvoiceNumber | Type | Name
Row1: | 1000 | Payor | Doe, John
Row2: | 1000 | Payor | Smith, Mary
Row3:| 1000 | Payee| Jones, Henry
Row4:| 1000 | Payee | Jones, Bob
I need to get those four example rows down to one row such as:
Header | InvoiceNumber | Payor | Payee
Row1: | 1000 | Doe, John & Smith, Mary | Jones, Henry & Jones, Bob
The number of payor/payee can vary between 1 and several. I need some way to loop through and keep appending the names in one cell separated by a "&". And then deleting all the duplicate rows (based on InvoiceNumber).
View 9 Replies
View Related
Jun 7, 2006
I want to copy a bunch of data from a text file and paste in into an excel worksheet I have open. I want to paste it at the end but I don't know how large the data range will be each time so I can't select that size range. I have this code so far:
FileToOpen = Application. GetOpenFilename("Text Files (*.txt), *.txt")
If FileToOpen <> False
Then
Workbooks.Open FileToOpen
Else
Exit Sub
End If
Cells.Select
Selection.Cut
Windows("myfile.xls").Activate
Range("A1").Select 'THIS IS THE OLD CODE FOR THE FIRST IMPORT
ActiveSheet.Paste 'I NEED TO REPLACE THIS WITH THE CODE FOR APPENDING
'OR PASTING AT THE END OF MY RANGE
View 9 Replies
View Related
Apr 24, 2013
I receive 24,000 text files once a month that need to be combined into one csv/txt file and/or spreadsheet(tab).
About a year ago I posted a thread on the same topic which received a fantastic response from jindon that worked great
Unfortunately, the format in which the text files are ouput has changed, as has the filename layout. The files are now output with filenames such as:
(lic#, company name, displaying # records found, date, type.txt)
40298827_Windham Professionals Inc _Displaying records 1 through 10 of 100_041813_AGENTS.txt
40298827_Windham Professionals Inc _Displaying records 11 through 20 of 100_041813_AGENTS.txt
40303726_HEARTLAND CREDIT RESTORATION INC _EANF_041913_AGENTS.txt
(files with EANF in the filename have no records inside them and can be skipped)
While the contents of each file look like this: (see attached text file reference)
I would like to combine the contents of the text files while appending the lic#, company name and date from the filenames to each record so the resulting file looks like this:
40305196 Audette , Anthony Sales Provider 40298827 Windham Professionals Inc 041813
40313800 Burritt , Kimberly Sales Provider 40298827 Windham Professionals Inc 041813
As far as I can tell jindon's code is fine except the regex expression needs to be modified to handle the new layout, however that is far beyond me.
View 9 Replies
View Related
Jun 12, 2014
Is there a formula that will add a number, in sequence, to the end of a text string to avoid duplicates?
I need to generate an ID number for transactions. This ID number is the Account Code-Last Two #s of the Year-Unique 3-Digit Number. So for instance, 5022-14-001 means it is the first transaction from account 5022 in the year 2014.
Column A has the Account Codes. Column B has the date of the transaction in MM/DD/YYYY format. So far the formula I have is:
=CONCATENATE(A1,"-",RIGHT(YEAR(B2),2),"-",TEXT(????,"000"))
With ???? being some function or set of nesting functions I need to create the sequential number. It needs to be able to say "Okay, this is the third instance of there being a 5022-14, so we need to stick -003 at the end of this."
Additionally, this "003" needs to be frozen, so if we change how the sheet is sorted and the line item moves around, it will still always be "003".
View 7 Replies
View Related
Jul 10, 2014
I have a column with a general format that looks like this: "057828001 - WACS - Irving".
I need to remove the latter part of the value " - WACS - Irving"
I am doing that by using the replace all and typing in "-*"; that gives me a result of 57828001.
I need a result of 057828001.
How do I retain the leading zero?
View 4 Replies
View Related
Jun 19, 2014
I have rows with "numbers" like 1 250,30 and 1 350,50, but they aren't in number format (I guess this is the problem). I am trying to get rid of the extra space between the "numbers" but the substitute or trim function does not work for me. I also tried to divide and multiply the numbers but it does not work.
I want to get the extra space of and to the number format.
View 9 Replies
View Related
Feb 14, 2014
I have a large spreadsheet that I need to take the spaces out within the text in each cell. Is there an excel function or macro that can do this? It would save me lots of time rather then having to go in manually and doing it!
View 3 Replies
View Related
Mar 28, 2012
I am trying to get spaces in text from several cells
A5 GH B1 YO E3 AF < I linked these cells with the & =A5&GH&B1 and it comes out like this GHYOAF
I would like it to come out like this : (either with or without the / or underscore in leu of a space)
Additioinally if I use the CONCATATE function it just showes the cells like this A5B1E3 ? Dont understand why its not showing the data rather the cell names & Numbers ?
View 9 Replies
View Related
Aug 17, 2007
I'm trying to use the SUBSTITUTE function to remove spaces from my cells in column A. The trouble is My formula is removing all the spaces from the cell. My formula is: SUBSTITUTE(A1," ","")
I do however have many cells that contain a space between characters 5 and 7 and spaces at the end of the cell which have been padded out to 15 characters long.Its only the spaces at the end of the cells I need to remove.
View 3 Replies
View Related
Jan 8, 2010
I have a formula that is dependent upon a column of cells containing text. Cells within this column randomly have an additional space (" ") following the words. With this invisible space, the formula doesn't work as intended.
Since I have an extremely long list of names, is there a way to easily remove additional spaces after words without manually going through each cell and deleting them?
View 9 Replies
View Related
Apr 5, 2013
I want to add blank spaces to a cell with text
My cells have up to 14 spaces
If a cell as 1 or 2 characters -I want to add 6 spaces in front of text.
If a cell as 3 or 4 characters-I want to add 5 spaces in front of text.
If a cell as 5 or 6 characters-I want to add 4 spaces in front of text.
If a cell as 7 or 8 characters-I want to add 3 spaces in front of text.
If a cell as 9 or 10 characters-I want to add 2 spaces in front of text.
If a cell as 11 or 12 characters-I want to add 1 spaces in front of text.
View 4 Replies
View Related
Jun 20, 2014
Please refer to attached file.
Column A have data and i need VB Code (Not formula) to check each cell in column A and delete any spaces and make sure that there is no spaces after end of the text.
As an example you can see Cell cell A15 thru A22 have spaces after the text so need to delete those spaces
Book1.xlsx
View 3 Replies
View Related
Aug 10, 2013
I need to take 12ABC1234512345 and turn it into 12A BC 12345 12345 with a vba macro. I've made a formula that works but in this situation a macro would be more ideal.
View 5 Replies
View Related