Copy Partial Strings From One Cell To Multiple Cells
Apr 5, 2007
I am trying to copy partial data from Column A into Columns B and C, then I want to delete Column A without affecting the results in Column B and C. Column A consists of a stock number in the format "1234-56-789-0000" (including the dashes) and is formatted as TEXT.
I'd like to paste the "1234" from Column A into Column B and the "56-789-0000" from Column A into Column C AND remove the dashes (-). I need to do this for the range (or number of rows) that is populated in Column A. This could be as many as 50,000+ rows of data. After this is done, I want to delete Column A. I tried using the LEFT and RIGHT formulas, but they are dependent on keeping Column A intact.
........... A .................. B .............. C ......
1234-56-789-0000 ..... 1234 ..... 567890000
and then eventually like this:
. A ............. B ......
1234 .... 567890000
On occasion, leading zeros in Column C have disappeared, and it's necessary it stay in a 9-digit format. There are 2 additional columns of data, but they shouldn't be affected by the above.
View 5 Replies
ADVERTISEMENT
Jul 31, 2009
1. Counts the number of cells in a column with the same 18-character text string(Full String is 24) using a RIGHT( function.
2. Returns a 1 if there is 1 and a zero if the count is greater than 1.
View 3 Replies
View Related
Jul 31, 2009
I don't know what's wrong with this it's not working..
=COUNTIF(SEARCH("radio",A2:A20),"")
i just want to count all the cells from a2 to a20 that has a word "radio" from that cells. Since a2 until a20 have different words like tv power, radio blaster (this should be consider because there's a radio word on it) and so on.
if i use only =COUNTIF(A2:A20,"radio") the word should be exact "radio" and it wont consider the word "radio blaster".
View 3 Replies
View Related
Jul 16, 2009
I have a 100 element header row. Each cell represents an account. I want to sum the amounts of each subsequent row if the word "saving" shows up in the header. For instance, there may be "saving 1" "Savings for Xmas" "Savings 2" in the header row, and I want those to trigger the sumif function.
Basically I want something like sumif(B2:B101,exact(left(B2:B101,6),"saving"),A2:A101)
View 3 Replies
View Related
Aug 16, 2013
I have a table with three columns, column 1 is a list of names, column 2 is where I want my result to appear, and column 3 has my array of search values. Column 1 and 2 could have any number of rows with values, but column 3 will have less values. It looks like this if it's working right:
Item List
Result
Search List
[Code].....
What I am trying to do is for each cell in column 1, I want to see if there is a matching value in column 3 - it has to be either an exact match or a partial match (i.e. the string appears somewhere in the values of column 1).
I have been trying to use MATCH like this:
=ISNUMBER(MATCH($A2,$C:$C,0))
but it does not return true unless the match is exact.
way to get these partial string matches to work?
View 3 Replies
View Related
Nov 19, 2007
I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:
a list of about 800 street names
a list of about 1000 property names
example
church street flat
church street apartment
house church street
house king's road
etc
what I would like excel to do is the following:
look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.
this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.
for example:
church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road
that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.
I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.
View 9 Replies
View Related
Mar 11, 2014
I have a spreadsheet with 1,000 rows of data.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.
- I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
View 2 Replies
View Related
Apr 22, 2013
I'm trying to build a formula that counts the amount of cells within a range that contain multiple strings of text within the same cell. I only know how to build a formula that snags cells that contain 1 but not 2 different ones within the same cell. For example: I want to count cells if they have the word BALL and STICK somewhere in the cell....see three cell examples below
gameballnetstick
ballgame
stick ball
tenballs
green stick
Of the three examples: it would only count cells: gameballnetstick and stickball
View 2 Replies
View Related
Oct 10, 2008
I have a database that I need to extract information from. Column "D" has a string that runs from 25 to 50 characters long, and I need to pull specific information out and need it to be inserted into a new column for each row of my database. The data that I need to pull from column "D" is always located between the first occurance of "=" and the first occurance of "&"
View 2 Replies
View Related
Jan 15, 2010
I have made a dynamic calculator which will calculate the total sale based on 3 criteria in 3 different cells as follows:-
I would like to derive the sales qty in cell C5 if all the 3 conditions in the range B2:B4 is true.
In this case the sales qty for Style-Colour-MRP combination(9103-PINK-399) should be 6.
I am unable to figure out with the formula for the same....
View 9 Replies
View Related
Jan 4, 2014
String Multiples for 3 different criteria in same cell -- "if", "or" "abs" --- not sure if any of these are correct
1) Criteria 1 and 2: In cell "E" I am wanting to minus "B from C", if "A" is greater than or equal to and less than "=>1000 or <15000"
2) and the other criteria would be if "A" is greater than or equal to ">=15000" then minus "b from d" -- answer would also be in the same cell "e".
I hope I am saying my needs correctly. Basically, I have 3 different sales criteria with 3 different bonus offers. One offer is for sales less than 10,000, another offer is for the range 10,000 to 14,999 and the last is for the range 15,000 or more. I am actually linking the values from different worksheets, however I have used generically "A, B, C, D and E".
below are the 3 separate formulas that do work on specific individual rows. However, I really need the criteria for all three variables in the same cell. Right now I am just copy/paste the appropriate formula that fits the specific value in each row.
=IF(summary!$E8>15000,SUM(F3-summary!D2),0)
=IF($E4<15000,ABS(F4-summary!D3),IF($E4>=10000,0,(F4-summary!D3)))
=IF($E14<15000,ABS(F14-summary!D13),IF($E14>=10000,0,(F14-summary!D14)))
View 6 Replies
View Related
Sep 16, 2013
I have a single column with multiple rows of data that contains multiple sports words (e.g.: football, basketball, track, hockey, swimming, skeet), and I want to find all cells that contain football, basketball and track. I also want to ignore case.
In this example only 3 rows would be found (3, 4, 8).
Sports Terms
------------
1. football, swimming, skeet
2. football, basketball, skeet
3. football, basketball, track, skeet
4. football, basketball, track, swimming, skeet
5. basketball, track, hockey, swimming
6. football, hockey, swimming, skeet
7. hockey, swimming, skeet
8. football, basketball, track, swimming
9. track, hockey, swimming, skeet
10. football, track, hockey, swimming, skeet
Here are 3 examples I found, but I do not even know where to insert them -pressing Cltrl+Shift+Enter does not do anything.
=IFERROR(IF(or(SEARCH("foottball",E2)>0,SEARCH("basketball",E2)>0,SEARCH("track",E2)>0)),"Yes",""),""),
=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")
=SEARCH((A1:A10=”football”))+SEARCH((A1:A10=”basketball”))+SEARCH((A1:A10=”track”))
View 5 Replies
View Related
Oct 28, 2013
I have some 100k+ cells with values as "ID_code - Value_1 - Value_2 - Value_3". Lets say this is sequence A.
"ID_code" is a fixed 6 digit value. "Name_1", "Name_2" and "Name_3" are variables and they also vary in length, but they never contain a sign "-". Sign "-" is only used as separator between these four values.
Now, VBA code that would rearrange all the values in a selected column from the one as listed above to a different sequence B, for instant:
Value_2 - Value_1 - ID_code - Value_3
View 3 Replies
View Related
May 23, 2013
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
View 7 Replies
View Related
Feb 1, 2014
I have two separate data files that I am trying to combine into one worksheet. One set of data looks something like this:
Column A Column B ColumnC
Date1 Name1
Date1 Name2
Date1 Name3
Date2 Name1
Date2 Name2
Date2 Name3
This is repeated for something like 200 dates, and there could be anything from 1 to 10 lines for each date, for a total of over 1000 rows.
The other data file has this information:
Column A Column B ColumnC
Date1 Place1
Date2 Place2
Date3 Place3
I.e. one line for each date.
I want to combine the data so that Column C contains the Place data. All I could think to do was combine them and then sort by Column A, so I ended up with this:
Column A Column B ColumnC
Date1 Name1
Date1 Name2
Date1 Name3
Date1 Place1
Date2 Name1
Date2 Name2
Date2 Name3
Date2 Place2
And then I would just copy and paste to end up with this:
Column A Column B ColumnC
Date1 Name1 Place1
Date1 Name2 Place1
Date1 Name3 Place1
Date1 Place1
Date2 Name1 Place2
Date2 Name2 Place2
Date2 Name3 Place2
Date2 Place2
Ultimately I want to sort the combined data by Column C, I just have to get the data in there first.
Is there a formula or macro or something I could use to copy the Place names automatically? Each worksheet with 1000 lines and several dozen worksheets means I'd have to copy and paste dozens of thousands of times, which is a bit tedious.
View 3 Replies
View Related
Mar 26, 2009
I have the following VBA code in Excel that looks in sheet called mri.txt for a cell called AcquisitionMatrix, Then goes one cell down and two cell to left and copy them all into Application.Workbooks(imgMain).Worksheets("Sheet1").Activate cell AC2, But for some reason it copies the fields name as well such as:
AcquisitionMatrix , 0, , 256, , 256,.
Is there a way to avoid copying the filed name into cell AC2? Such as :
0, , 256, , 256,?
here is the
View 2 Replies
View Related
May 12, 2009
I have a spreadsheet that contains data that is currently split up into 2 or more cells. Sometimes it's 2 cells, sometimes 3, even 4, etc. I would like to right click on multiple adjacent cells, select copy, right click on another cell and paste these multiple cells into 1 cell. I checked paste special but didn't really find anything. I'm not sure a formula/macro would work either as I need to manually determine which cells need this attention.
View 2 Replies
View Related
Mar 1, 2012
I have to take data from about 10 rows and then paste them into ONE cell . . . any easy way of doing that without VBA or concatenating?
Example -
Move -
Jeff
Bob
Joe
Mary
into Jeff Bob Joe Mary
View 1 Replies
View Related
Feb 21, 2009
I want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.
i'm looking forward to hearing if and particuliar how this could be achieved.
View 13 Replies
View Related
Apr 15, 2014
I am using Excel 2010.
At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.
17,43,61,63
17
43
61
63
23,29,53,57,77,79
23
29
53
57
77
79
17,29,63,69,71,75,79
17
29
63
69
71
75
79
11,43
11
43
57
57
I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.
View 5 Replies
View Related
Jun 13, 2014
I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
Is there a way to do this with the VBA code.
View 3 Replies
View Related
Oct 7, 2008
I'm attempting to copy some files into an archive folder in the same main folder. These files appear weekly in a folder that contains numerous other files.
Each of these files has a filename such as:
RAH_MRL_TOP_40_(ddmmyy_TIME)
with time being the time that the report was run.
Every week the filename stays the same but the time and date changes. I was hoping to be able to simply use the "Filecopy" command and specify the source file with a "*" after the main file name. However this does not seem to work.
Is there a simple way to do this? (I dont want more than a few lines of coding really as I will be duplicating it for each file and then changing the file name as needed.
View 9 Replies
View Related
Feb 4, 2012
I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.
If the exact phrase match is found, return the value True or False.
Column A
A1:Dog likes to run
A2: Cat Red-tummy-01
[Code]....
View 9 Replies
View Related
Apr 5, 2012
I am looking for a macro that will insert a new row and copies the formulas from the row above, but not from all the columns.
This is the current situation:
A
B
C
D
E
F
G
H
I
J
K
L
M
Name
Project Nr.
Work Order
hours
[Code] ......
What I want is that if column G contains "11374340" an empty row will be inserted above and the formulas from column E until M to be copied a row down. And the sum function in the last row of M needs to be changed automaticly as well (if possible).
I already have the macro to insert a new empty row which is as follows:
Sub InsertRows()
Dim i, LastRow
LastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "G").Value = "11374340" Then
Cells(i, "G").EntireRow.Insert
End If
Next
End Sub
View 4 Replies
View Related
Feb 19, 2008
I have data in columns a - d; the number of records can vary from a few to a huge number which (to me) makes the problem more complex.
I need a macro that will copy every 3rd record into rows e - h; and every 5th record into columns i - l, etc.;
View 9 Replies
View Related
May 27, 2014
Basically, i have a workbook that contains 2 worksheets with stock data for two stock holding sites, we're consolidating our stores and need to confirm what stock is held at both sites. I need to compare column C on both sheets to find if an item exists on both sheets, and if so copy the entire row from Sheet 2 onto the next free row on a new sheet. Col C on each sheet contains the stock number, however, on Sheet 1 the stock number is abbreviated ot the final 7 letters, with Sheet 2 showing the FULL stock code.
I need whatever is in "Sheet 1:Col C" (for example '1234567') to be compared to whatever is in "Sheet 2:Col C", and if a match is made (for example 9999-00-1234567) then the row containing the match be copied to the next free row on Sheet 3.
View 1 Replies
View Related
Jan 3, 2013
I have a questions regarding extracting records using a partial text search in multiple columns.
I would like to do a partial text search of the letter "j" in the Name column, and search for the number "1" in the Phone column and extract the corresponding records.
The formula I have been using is as follows: =IF(ROWS(C$2:C2)>$B$2,"",INDEX(C$2:C$6,SMALL(IF(AND(ISNUMBER(SEARCH("J",$K$25:$K$29)),
ISNUMBER(SEARCH("1",$L$25:$L$29))),ROW($J$25:$J$29)-ROW($J$25)+1),ROWS(C$2:C2))))
However, the formula is not working correctly.
find my sample data below:
A
B
C
1
Partial Text Search
[Code]....
View 5 Replies
View Related
Mar 14, 2013
On one workbook I have "column A" that lists serial #'s, one serial number per cell. What I need is to be able to take all the non blank data from that column and copy it to another workbook with all those serial #'s in 1 cell numerically sorted and separated by commas.
View 1 Replies
View Related
Jun 16, 2006
I have a macro which makes two new WS (AU - UA and AU - LH), copies header row to both of them and then copy three letter airport codes which are in column A with appropriate prices to these new sheets. I need to modify code so if there will be in pricelist some new airport codes which are not in macro yet, that they would be copied to new WS (NEW CODES), so I will know which airports are new. I have attached example file, to make things clearer what kind of result I would like to get after running macro. Here is code of my macro:
Option Explicit
Sub UA_LH_OS_SK_VER_2()
'Turn off ScreenUpdating for faster runtime so screen won't flash while running
Application.ScreenUpdating = False
Dim CLL As Range, FaresWS As Worksheet
Set FaresWS = Sheets("FARES")
Worksheets.Add(After:=FaresWS).Name = "AU - LH"
Worksheets.Add(After:=FaresWS).Name = "AU - UA"
With FaresWS.Rows(1)
.Copy Sheets("AU - UA").Rows(1) ' Paste to worksheet AU - UA
.Copy Sheets("AU - LH").Rows(1) ' Paste to worksheet AU - LH
End With.....................
View 4 Replies
View Related
Jul 11, 2014
How to check for a partial match looking in one cell, A1 for example, and checking to see if the contents of B1 show a partial match (example attached).
I can't use the vlookup because I only want to look at cells specifically, not a table/list. Please also note that the attached example has no formulas.
Forum Example.xlsx
View 5 Replies
View Related
