Combining Cells (strings With Spaces)
Nov 21, 2013
I have the below data that I need to concatenate, merge... I'm not sure.
The data looks like this currently:
So that it reads in one sentence, e.g. Northumberland; Newcastle; North Tyneside; South Tyneside (note - no "." or ";" at the end of the string).
I had come up with this formula -
=IF(A11>0,A11&"; ","")&IF(B11>0,B11&"; ","")&IF(C11>0,C11&"; ","")&IF(D11>0,D11&"; ","")&IF(E11>0,E11&"; ","")&IF(F11>0,F11&"; ","")&IF(G11>0,G11&"; ","")&IF(H11>0,H11&"; ","")&IF(I11>0,I11&"; ","")&IF(J11>0,J11&"; ","")&IF(K11>0,K11&"; ","")&IF(L11>0,L11&"; ","")&IF(M11>0,M11&"; ","")&IF(N11>0,N11&"; ","")&IF(O11>0,C11&"","")
Which works fine if there is a value in cell O, but if not, then a semi-colon appears at the end of the string. It also seems incredibly clunky.
Basically, I'm struggling (being a total n00b) to get the semi-colons in the right place, blank cells to be skipped or not included, and for there to be no semi-colon after the last value.
View 4 Replies
ADVERTISEMENT
Jul 22, 2006
I have created (via recording) a very simple macro in Microsoft Word to convert a fraction of the form a/b to the form where a is directly over b with a horizontal line between them (like how you would actually write it in math class). Before I send it to my co-workers, I want to beef it up a little.
Right now, it will only work if a and b are integers or if a and b are words or phrases with no spaces (i.e. change/time will convert correctly but the phrase change in x/change in y will not). Also, it would be great if it could work with parenthesis (i.e. (2x-1)/(2x+1)).
Just so you know, I am very comfortable working with code in order to come up with a beefier macro. However, Word uses Visual Basic and I'm afraid I don't know much about the syntax of that code. So, if any of you know of a site I could view to learn a little Visual Basic or if you know a site that already has good code already written for this purpose, I would be much obliged.
View 8 Replies
View Related
Sep 16, 2009
I recently had a query about pulling data from different closed workbooks using a concatenate to easily choose the file:- ....
View 6 Replies
View Related
Jan 26, 2007
I am trying to come up with a formula which would combine two groups of numbers, put them in numberial order and remove duplicate numbers:
3 6 10 22
1 3 5 9 10 13 19
The above two groups of numbers are in separate cells in two different rows.
the result would also be in a different row: 1 3 5 6 9 10 13 19 22
View 14 Replies
View Related
Jun 12, 2014
is there a way to reduce spaces between text stings to one space only when there are many spaces? To make it worse, the number of spaces between the text strings vary. I am using Excel 2010.
View 9 Replies
View Related
Jun 20, 2014
I'm looking for a function (I'm thinking 'SUMIF') that will search through column A of Sheet 1 and add column B of Sheet 1 when the cell in column A contains one of the strings listed in column A of Sheet 2. I can only find information when searching for single strings or cells.
View 8 Replies
View Related
Dec 20, 2012
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
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
Oct 20, 2008
Let's say in a cell i have the text ABCD/0123/01234 or ABC/0123/012345
How would i go about extracting whatever is inbetween the / ? i.e. 0123 in both examples?
View 14 Replies
View Related
Dec 29, 2011
I have a column of numbers that are in sets of 3. (123 456 789) I need to remove these spaces and just see 123456789.
View 3 Replies
View Related
Dec 4, 2007
Public Function DSPACE(ByVal OCELL As String) As String
Dim sC As String
Dim i As Integer
For i = 1 To Len(OCELL)
sC = Mid(OCELL, i, 1)
If (sC >= "0" And sC <= "9") Or (sC >= "A" And sC <= "Z") Then
DSPACE = DSPACE + sC
End If
Next i
End Function
View 4 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
Sep 23, 2008
I have a data table which has a mixture of text, blank cells, and cells with spaces in. I have created a pivot table from the data table. The pivot table is counting the cells with spaces in instead of reading them as blank. I have been able to fix this by putting on a filter in the data table, highlighing all the blank cells only and then clearing them column by column.
The problem is the data table is 50 columns long, so it is quite time consuming to do this manually. The data table gets updated regularly so this is an ongoing problem. Does anyone know of a faster solution to fix this problem? I have attached a snapshot of the problem as an example
View 4 Replies
View Related
Oct 4, 2007
I Need To Combine 2 Fields Into 1 Field Without Spaces Or Commas Between Them.
View 4 Replies
View Related
Oct 25, 2007
I am working with a spreadsheet and rather new to be VBA. How do I select a range that only has data. I currently have the following macro, but when I run it, it checks every cell in the active worksheet which cause the application to hang. I would like it to automatically select only cells that have data in them ignoring all empty cells. I need this to be an automatically process running without the user selecting a range of data.
Here is my code ..
View 8 Replies
View Related
Sep 28, 2009
I know a simple formula would 'almost' do what i want...but i was hoping for a simple feature....
i have several text cells that i want to combine into one cell.....
so a formula like =A1&B1&C1.... would work fine....but is there anyway to present this data a little fancier? for example separating each cell entry after a "break" or something....
View 9 Replies
View Related
Dec 16, 2013
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
View 9 Replies
View Related
Jun 24, 2014
I have attached what my problem is. Have a look, and let me know what you think...have spent hours trying INDEX, MATCH, IF, ISTEXT...
JamesExcel problem.pdfExcel problem.pdf
View 2 Replies
View Related
Jun 16, 2009
I'm compiling several old worksheets into a single database for a research study. The worksheets contain patient data. Some of the worksheets have the patient's last name, first name, and middle initial entered all in one cell like this: Smith,John R. Compounding the problem is the fact that sometimes the name is entered with a space between the comma and the first name, sometimes not.
I have formulas to break the name out into three separate columns "lName, "fName", "midInit". However, if the name in the original cell has a space between the coma and the first name, then the “fname” column will contain a blank space in front of the name. This is problem because patients names can appear in the database more than once. Some patients are in the database several times.
If patient “John R. Smith”, for example, is entered in the database as”
“Smith” “John” “R “
and also as
“Smith” “<space>John” “R”
then the database won’t recognize them as the same name when I search for John Smith’s data. Right?
If so, then I need a way to eliminate the empty spaces in front of the first names. Like I said, some have empty spaces and some don’t. I could do this by hand, but there are over 1000 entries in these worksheets.
View 2 Replies
View Related
Aug 18, 2009
I need to take data from a cell that has a space "cherry banana", and make it into two cells "cherry" and "banana". I want to duplicate my line on which the data resides, if possible.
What I have is a sheet like this:
View 5 Replies
View Related
Aug 12, 2013
I want to copy A1 and C1 (there is data in A2), but when I copy it somewhere else, I want it to maintain the column space with NO data. Can I do this?
View 1 Replies
View Related
Aug 19, 2014
I am having trouble trying to figure out how to insert blank cells:
I need to insert cells, in columns B through H, equal to the number of returned IDs in column B.
Please take a look at the spreadsheet I attached.
Here is the code I am using to separate the IDs in column B so that I get one ID in each A cell. When I do this it separates them great, but then I have to manually go in and insert cells down so that the rest of the rows match up with the right ID.
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
[Code] .....
sample of separate.xlsm
View 2 Replies
View Related
Oct 16, 2013
how to remove multiple leading and trailing spaces while leave spaces in the middle of the string in place? I have tried text to columns but this does not remove the multiple spaces.
View 9 Replies
View Related
Jan 24, 2008
I'm have a list of keywords from google adwords:
cabinet hardware
cabinet hardware knobs
cabinet hardware pulls
kitchen cabinet hardware
amerock cabinet hardware
antique cabinet hardware
These are all in A1, B1, C1, D1, E1, F1
I need to have them look like this: A1 cabinet hardware, cabinet hardware knobs, cabinet hardware pulls, kitchen cabinet hardware, amerock cabinet hardware, antique cabinet hardware. How do I do it? Im a COMPLETE Newbie when it comes to excell but I urgently need to figure this one out....how do I do it?
View 4 Replies
View Related
May 27, 2008
While entering data space is given in the first and last of each cell content. For example
if there is a word Alex Patrix in a cell, space is given before A of alex and after x of Patrix. This is done fo many cells. I want to remove only initial and last space which is un-necessary.
The space caused problem to compare cell so i've to remove space.
I've around 2500 cells with this problem.
View 2 Replies
View Related
Mar 16, 2013
I am trying to delete entire row that has certain text, let say "hi". I was able to delete cells with exact word "hi", but was not able to delete cells that have texts other than "hi". ex) "hi myname." Below is my vba code, and I keep getting run-time error and cannot execute the code.
VB:
Sub HiDelete()
Dim srchRng As Range
Set srchRng = ActiveSheet.Range("g:g")
For i = srchRng.Rows.Count To 1 Step -1
If worksheefunction.Search("hi", Cells(i, 7)) > 0 Then Rows(i).Delete
Next i
End Sub
View 3 Replies
View Related
Jan 9, 2013
I have a series of incident reports (some of which are injuries), and I'm calculating the number of incidents per body part per month using:
=SUMPRODUCT((ISNUMBER(FIND($D4,owssvr!$Q$2:$Q$2188)))*(MONTH(owssvr!$A$2:$A$2188)
=MONTH(math!F$2))*(YEAR(owssvr!$A$2:$A$ 2188)=YEAR(math!F$2)))
In which D4 is the body part (Neck, for example), owssvr is the sheet with the records being summarized and F2 contains the month being queried.
The problem I'm having is that I want to calculate data for a region of the body (head & neck), which will include count any record that has a part of that body region (nose, face, eye, tooth) mentioned in the affected area text. If I simply sum all the values calculated for each body part, records that include multiple parts (e.g. "scratched nose and eye") will be counted twice.
Can I calculate whether a range of cells for the incidents contains any of a specified range of body parts (listed in D4:D15), but do this for each month, and count each record only once (e.g. "cut nose" = 1 record, "cut nose & eye" = 1 record).
View 1 Replies
View Related
Apr 29, 2009
I have two columns, Brief Descriptions and Detailed Descriptions. In many cases they are different and I combine the two for one longer description; however in certain instances the two columns read the same. Is there was way to combine the two cells where any repeat text is only expressed once?
View 3 Replies
View Related
Dec 29, 2009
I'm trying to combine 2 cells: 1/1/09 and ABC. However, when I combine them the date keeps changing to a number. Is it possible to keep the date format?
View 2 Replies
View Related
Sep 17, 2009
I am trying to combine multiple cells into 1 cell per row.
I would have "A" column empty, then combine "B", "C", and on into the "A" column.
Each cell value will be separated by a space or any special character I designate in the macro
I have attached an example which the output is separated by a space. Sheet1 has the original file and Sheet2 has the desired output.
View 9 Replies
View Related
