Combined Numbers And Texts Without Delimiter
Aug 23, 2012
My excel sheet contains inventory of products and quantities, each is in a form of combined number followed by text without a delimeter. For example 2AWS means Two (2) of AWS.
Each cell in my excel database may contain pair of products, each is defined in the above form but the pair of products are separated with a comma.
I need to calculate the total number of each product and split in two different cells.
For example:
Cell A1: 3GFG, 2AWS
Cell A2: 2NG, 1AWS
Cell A3: 1NG, 4GFG
Desired Output
==============
GFG7 (i.e. Cell A5: GFG , Cell B5:7 etc.)
AWS3
NG3
View 3 Replies
ADVERTISEMENT
Aug 24, 2012
My excel database sheet contains inventory of products and quantities, each is defined in the form of combined number followed by text without a delimiter. For example 2AWS means Two (2) of AWS.
Each cell of the inventory field may contain up to two products, each product is identified in the above format; but the pair of products are separated with a comma.
The inventory field is occupying from cell $A$2:$A$5000; and is defined as a named range "INV_QTY" with scope within the entire workbook. I need to calculate the total inventory for each product.
Here's an example:
Cell A2: 3GFG, 1AWS
Cell A3: 2NG, 2AWS
Cell A4: 3AWS
Cell A5: 4QLK, 4GFG
etc.
The desired output should be calculated as follows (assume Input will be on Column C & D)
Product - QTY
GFG - 7 (i.e. Cell C2: GFG , Cell D2:7 etc.)
AWS - 6
NG - 2
QLK - 4
View 7 Replies
View Related
Apr 29, 2009
I've a cell that contains numbers and texts and I want to sum the numbers and get a result in another cell.
This is an example for my cell:
arando-3,12; hegymeg-4,3; vas-7; aranyoska-1,8
and the result: 16,22
View 9 Replies
View Related
Jun 25, 2014
We are changing management systems here and in order to do so we need to provide the names and addresses of over 400 different people. Our current management system stores people's addresses as a whole for example; 32, Example Street, Town, County, Postcode.
I cant extract just the numbers because I only need the house number and not the postcode.
After searching several forums I thought I had solved it with this.
Cell A1 is the extracted address from current management system reading 32, Example Street, Town, County, IU13 8TY
Cell B1 is =LEFT(A1, SEARCH(" ",O15,1))
Cell C1 is =SUM(MID(0&B1,LARGE(ISNUMBER(--MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))*ROW(INDIRECT("1:"&LEN(B1))),ROW(INDIRECT("1:"&LEN(B1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B1)))/10)
That only gives me the number "3" though and I need 32.
Basically A1:A400 will be addresses as above and in cell B1:B400 I need just there house number at the start.
View 3 Replies
View Related
Jun 27, 2014
I have column A with Different status like Open, closed, available etc. Column B have numbers. i am looking for a formula in condition formatting for column B.
If the cell value in column B is 2 or more, the same cell should be highlighted in Green.
If the cell value in column B is 1, the same cell should be highlighted in Yellow.
If the cell value in column B is 0, the same cell should be highlighted in Amber.
If the cell value in column B is less than 0, the same cell should be highlighted in Red.
If the cell value in column A is "Available", cell in column B should be highlighted in Amber.
View 5 Replies
View Related
May 7, 2013
I have excel data where it runs like below:
Lumina
006
Ali Rashid Al Amin
61.40
Date
03/04/2013
11/04/2013
14/04/2013
check
20
Raja
I need a result column containing as below but where the numbers are removed those cells has been
Lumina
Ali Rashid Al Amin
Date
check
Raja
View 4 Replies
View Related
Jul 9, 2014
I need to organize some unorganized texts in my columns.Here is what I have in the column:
STARMERBRIEANA 134 CRICKLEWOOD AVE HENDERSON,NV 89002 CLARK Home - ( ) - Work - ( ) - Ext.
Here is what I need it to look like:
Column B = "134 CRICKLEWOOD AVE HENDERSON,NV"
Column C = "89002"
View 10 Replies
View Related
Jan 24, 2009
I have hundreds of numbers in column A and I want to combined these numbers in call B1 with “ , ”
e.g.
column "A"
451
45
61
48
194
Call "B1"
451,45,61,48,194
I am using attached sheet for this thing but I can’t use any other new sheet, is there any another way to do this. I need formula. So I can use any sheets.
View 10 Replies
View Related
Feb 14, 2008
I have a listbox populated with a number followed by description. How do I write code to extract the number and description to different cells. Listbox example: "1234ES - Project Description". Now I want "1234ES" in one cell and "Project Description" in cell left of number.
View 2 Replies
View Related
Feb 16, 2014
Actual
Result
london#123;new york#34;
london;
new york;
delhi#145;chennai#54;
delhi;
chennai;
hyderabad#748;
hyderabad;
[code]....
removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.
View 9 Replies
View Related
Oct 14, 2009
I have the following in cells a1,a2,a3 & a4 as follows:
A = 14
B = 15
C = 16
D = 20
Total = 65
I need to sum the total of the above, a,b,c & d and show the total. How do I add only the figures and get the total?
View 9 Replies
View Related
Dec 1, 2009
Got the following data in different cells:
And on a different sheet in the same workbook, have 45 different values to be converted:
What is required?
Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.
Basically, for the 1st Cell for eg..
2232-2232-
I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.
Result shall look like below:
20-20-
Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:
2210-22G1-45R1-45G1-20R1-20GP-
It becomes difficult for me. What i need exactly is:
Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.
View 9 Replies
View Related
Jul 2, 2013
I have created a hierarchy chart using smartart. Is there a way to hyperlink the texts in each box to other sheets in the workbook?
[URL]
View 3 Replies
View Related
Jan 11, 2010
I would like to create a formula that allows the destination cell display a number depending on a word that is typed in the source cell. The source cell and the destinating cell will remain the same two cells.
example:A2=IF(A1="WORD1",1.5,0)
If i type WORD1 in A1 (source cell) then A2 (destination cell) will display 1.5, but how do i make this formula work for a multiple of different words.
example:A2=IF(A1="WORD1",1.5,0 IF(A1="WORD2",3.0,0 IF(A1="WORD3",2.5,0)))
this formula doesn't work, but is basically what i am looking for the formula to do - WORD1 displays 1.5, WORD2 displays 3.0, WORD3 displays 2.5 etc where the source cell and the destinating cell remain the same two cells, it is just the word and its value that change.
View 4 Replies
View Related
Dec 15, 2012
I am looking for a macro to delete rows that DO NOT contain criteria. But what I could achieve so far is deleting all rows except the first word ie ACC here. My code so far is as follows
[Code] ..........
View 12 Replies
View Related
Aug 1, 2008
I have two texts in two cells. e.g in A1 I have JOHN, and in B1 I have SMITH.
I need to cocatenate these two texts in a third cell (=CONCATENATE(A1, B1) /or I can use = A1 & " " & B1). It's fine till I concatenate.
But I need the output in the following format:
JOHN SMITH
The second text needs to be in ITALICS.
View 14 Replies
View Related
Jul 12, 2014
I have a lookup table in which names of students and the planned session dates are available.
I need to create a chart that has Student Name and Each date of month. The chart should have value of 1 wherever a match is found in the look up table.
I am wondering if combination of searching for multiple strings (student name and date ) will work somehow. The below image explains my problem
StudentTimeTable.png
View 5 Replies
View Related
Jul 29, 2013
What I have: (Table1) A table with multiple columns. One column consists of cells that can contain different types of information in a non-consistent way. One thing is for sure: if a cell contains data, it will always contain a name of a person. E.g. a cell in that column could contain the following: "838374987343 based on 98989 John 990999", or "000000000john", or "William 39843984 more text and even more text"
(Table2) A separate table that contains the names of people in a column, e.g. "John", "William"
What I want:A formula that searches for the names (mentioned in Table2) in Table1 and copies this name in a new column.
Result:A new column with the copied names as content.
View 2 Replies
View Related
May 26, 2014
I have a very large database that contains names with minor differences on column A such as:
A
EXCEL AMERICAN COMPANY S.A.
EXCEL AMERICAN COMPANY SA.
EXCEL AMERICAN COMPANY, S.A.
ASSOCIATION, INDUSTRIAL
ASSOCIATION INDUSTRIAL
L.A. COMPANY CO
LA COMPANY CO
As you can see, this makes a mess when dealing with numbers associated with them and makes analysis more difficult.
What I've been trying to figure out is how to quickly get in column B a standard name for each one, for example:
B
EXCEL AMERICAN COMPANY S.A.
ASSOCIATION INDUSTRIAL
L.A. COMPANY CO
I was thinking of using a combination of the FIND and EXTRACT formula or maybe LEFT as well. (Maybe VB?)
View 6 Replies
View Related
Jan 10, 2008
i have wordlists which i would like to enhance through texts. For that purpose i need a macro which reads a text and splitts it into words and put the words into three columns. Lets assume the column where the words are supposed to be put is columns A,C and E. The column A should contain single words containing no spaces from Text, column C should contain compound words which have one space between them and in column E those compounds which have two spaces. Now i will put my existing wordlist in column G before i start splitting texts. The macro should put only those words from text into column A,C and E which dont already exist in columns A,B,C,D,E,F and G. In Column B i will place the corrected or checked words from column A. In column D i will type the corrections and checked versions of those compunds existing in column C. And the column F will contain the compound words which are corrected and checked from Column E.
Column A--> single words splitted from text through macro (containing no spaces)
Column B--> corrected and checked versions of words from column A through user
Column C--> compound words containing one space splitted from text through macro (for example:"etwas möchten" or "do something")
Column D-->corrected version of C through user
Column E-->compound words containing two space splitted from text through macro (for example:"sich uerberzeugen lassen" or "down in dumps")
Column F-->corrected E
Column G--> existing dictionary wordlist through user
Signes like ".", ":",";", "/" or quotation signe itself should be seen as seperator. And the words or compounds splitted from the text should not contain these signs.
The text will be put in another sheet in the same excel file. Lets call that sheet "text". The macro should search for words through whole sheet ignoring empty cells or columns.
None of the columns should be filtered or ordered. The columns shound not be deleted as well. That way the user wont have to correct the same word each time cus the corrected as well as the original words will be there.
A thread with similar purpose was made sent by AdamDay as well, where rylo has posted a good solution already.
View 14 Replies
View Related
Feb 25, 2013
I need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.
I have three columns; Date, Company name and Payment mode.
Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234
View 1 Replies
View Related
Feb 16, 2014
I want to color the cells for some specific texts in RED, and some in BLUE and some in GREEN.
For Eg: I want to color the cells in RED, which contains below specific texts in a sheet.
UNIX
LINUX
MS OFFICE
POWER POINT....like this for nearly 100 specific texts.
and I want to color the cells in BLUE, which contains below texts in a sheet.
JAVA
.NET
SQL
MSDOS....like this for nearly 100 more specific texts.
If i create a rule in conditional formatting for each text,I have to create nearly 400+ rules for my task now, which is not possible.
View 5 Replies
View Related
Jun 18, 2014
I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.
Code to fill these blank cells automatically with some specific texts based on the attached conditions.
I have attached the workbook for more details. FillCellsTest.xlsm‎
View 10 Replies
View Related
Jul 3, 2008
I have options buttons in a userform, first is "$" the other is "€"
When somebody chooses one of them, it writes the choice to a cell in the data sheet. But when clicked on the next userform I want some of the texts automatically change the currency depending on the choice made in the previous userform.
The formula below was is example
=""&Data!$A$2&" / m³"
=""&Data!$A$2&" / kWh"
View 9 Replies
View Related
Mar 6, 2008
I have one field that contains a city, state and zip. I need to extract only the city. I need the proper function to ask Excel to go to the cell and return all the text beginning the first place on the left, continuing until it reaches a comma. The number of places in the city will always vary.
View 9 Replies
View Related
Jan 31, 2009
to concatenate across columns, say J through Q, (some of which are blank) and have the results delimited by a comma without returning anything for columns that might be blank. So R660 would read "0263B001A,FX9,FX-9,FX10,FX-10" and R669 would ...
View 9 Replies
View Related
Apr 21, 2009
I'm looking for a worksheet function that will allow me to separate into another cell the characters between the second and third slash in the source cell. The number of characters varies, but the value I want is always between the second and third slash.
View 3 Replies
View Related
Nov 11, 2008
I have a column with cells that look like: XYZ - JobABC - Area 1-A. I'd like to split the column into to and have it look like: XYZ <next column> Job ABC - Area 1-A
Normally I'd use the text to columns function and "-" as delimiter, however, there are other instances of "-" which I do not want separated. Note that the XYZ can vary in length and therefore fixed width will not work either. I have attached a small sample with the different variations that I could encounter.
View 7 Replies
View Related
Jul 17, 2007
I'm trying to check that part of a delimited field has been entered with a date format of DD-MON-YYYY and I cannot work out how to do this.
The following is what I have so far:
Public Sub date_val()
Dim rCell As Range
Dim RowCounter As Integer
Dim ColumnsChecked As Integer
Dim InvoiceDate As Date
For Each rCell In Range("B14:B5000")
RowCounter = rCell.Row
ColumnsChecked = 41
If UBound( Split(Cells(RowCounter, ColumnsChecked).value, "|")) <> 11 Then
MsgBox "Incorrect number of delimiters. Please correct", vbCritical, " Validation"
Cells(RowCounter, ColumnsChecked).Select
End
Else
End If
View 4 Replies
View Related
Mar 7, 2008
I receive multiple worksheets containing transaction data with multiple numbers of row and columns. The data columns contain varying types of information like: price, quantity, location1, location2, loading dock, transaction date, buy/sell, purchaser, seller, currency unit, etc.
I have a need to concatenate into a single cell in a blank column, data from a number of cells in each row -- sometimes two cells, sometimes more. In some instances, I'd like to concatenate data for location1 and location2 with a delimiter like a semicolon-and-a-space to create a cell with: "location1; location2" Or in another instance, I'd like to concatenate multiple cells in a row, like: "location1; price; quantity; transaction date", etc.
I'm looking to create a macro that, with the cursor in a blank cell in a blank row, would open an input box, ask a user to select with the cursor the cells in the row to concatenate and then concatenate them into the blank cell in the row.
Ideally, the macro would populate the entire blank column with the concatenated data for the group of selected cells (from the same columns as in the row where the cells to be concatenated were selected) for each row where there is data. It is also possible that some cells in some rows of the selected columns would contain no data. In that case, I'd be looking to return just the delimiter, like: "location1; ; quantity; transaction date"
Right now, with no macro, I do it by hand like this:
To concatenate into cell I6, the contents of cells A6, B6, G6 and H6, I do a "=CONCATENATE(A6,"; ",B6,"; ",G6,"; ",H6)" in blank cell I6, but I'm looking for a way to do it fast in a macro that would fill in all of Column I.
View 5 Replies
View Related
