Extracting Characters From Cell
Oct 5, 2008
I have a spreadsheet containing 2 columns. Column B contains a list of item numbers which are made up of either all numeric values or alpha numeric values. Column A will contain the vendor code which must be extracted from the item number in Column B based on the following criteria:
a. If the item # in Column B begins with the letters ZZZ and the next four (4) characters immediately following the ZZZ letters are ALL numeric, then the vendor code = the next four characters immediately following the ZZZ.
b. If the item # in Column B begins with the letters ZZZ and the next three (3) characters immediately following the ZZZ letters are alpha numeric, then the vendor code = the next three characters immediately following the ZZZ.
c. If the item # in Column B begins with the letters AMER, then the vendor code = AMER.
d. If the first four (4) characters in the item # in column B are all numeric values then the vendor code = the first four numeric characters from the item #.
e. If the first three (3) characters in the item # in Column B are alpha-numeric values, then the vendor code = the first three alpha numeric characters from the item # in Column B.
I tried writing the following formula and receive a #VALUE! error message:
=IF(AND(LEFT(B3="ZZZ",ISNUMBER(MID(B3,4,4)*1)=TRUE)),MID(B3,4,4),IF(AND(LEFT(B3="ZZZ",ISTEXT(MID(B3,4,3)))=TRUE),MID(B3,4,3),IF(LEFT(B3,4)="AMER","AMER",IF(ISNUMBER(LEFT(B3,4)*1)=TRUE,VALUE(LEFT(B3,4)),LEFT(B3,3)))))
View 9 Replies
ADVERTISEMENT
Feb 17, 2014
i have 8 digit invoice numbers. Each 8 digit number starts with one of three 4 digit ID codes. Depending on the first 4 digits, I want a different "company" to show up.
First seems to work fine.
=IF(E6>"1NCC","North Carolina",IF(E6>"2ALA","Alabama",IF(E6>"3SCC","South Carolina")))
View 3 Replies
View Related
Jan 7, 2007
Is there a way of extracting all non alphanumeric characters from a single collumn?
After extraction, I just want strings with A to Z letters, 0 to 9 numbers, spaces and hiffens(-). All in lower case.
View 9 Replies
View Related
Dec 3, 2013
I am trying to extract text from stings that appear in the following format XXX-XXX-XXX and XXX-XXX-XXX-XXX.
I have figured out how to extract the first and second sets of data, what I am stuck on is getting the third and (if present) fourth sections.
View 6 Replies
View Related
Apr 28, 2006
ive search the forums for this particular need and found somthing close but not what i need. i need to write a formula (not VBA) to extract a string of letters which can very in number out of a string of characters in a particular cell.
ex. cell A1= 00abcdefg00-000-00
cell A2 = abcdefg
i've tried different methods but can seem to come up with anything.
View 4 Replies
View Related
Dec 14, 2013
I have the data string below:
Career:25: 1-0-2 $13,765
I would like to extract the 1 between the : and - and as a seperate extraction would like te 2 between the - and the $ I have tried a few things but end up with the - as the length of the data changes
View 5 Replies
View Related
Mar 20, 2014
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
View 4 Replies
View Related
Aug 1, 2014
So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.
So:
Charles Johnson -> Johnscha
John Smith -> Smithjoh
Willian Wu -> Wuwillia
What's the best way to do that without creating some crazy formula with tons of if/thens?
View 4 Replies
View Related
May 29, 2009
In one column (Column A) I have a column of data which contains long descriptive text - i want to extract a specific piece of text from this descriptive text and enter it into the next Column (Column B).
E.g. Cell A1 = "Hello Buddy, how are you?" - i want to extract the word Buddy and enter it into cell B1
then A2 into B2, A3 into B3, etc, etc,...
Is there a simple macro out there that can do this?
View 7 Replies
View Related
Jan 17, 2012
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
View 8 Replies
View Related
Dec 26, 2013
I have a cell that contains values such as "AAPL" or "0001.HK" (without the quotes). I would like to extract the content to the left of "." (if any). i.e. I want the cell to return "aapl" or "0001". What formula should I use?
View 9 Replies
View Related
Jan 1, 2014
I'm trying to extract the first name from each row in column a using:
Formula:
A B
Joe Smith Joe
Julie Smith Julie
Julie #VALUE!
If the cell does not have 2 names it's giving me an error.
View 10 Replies
View Related
Oct 13, 2008
If I have some data in a cell (let's say D1) such as "m-3,s-2,xl-4", what formula can I use in say cell D2 so that if I find the characters "s-" in Cell D1, then it will return the value "2".
Similarly, in say in Cell D3, I would then want to use the same formula but modify it so that if the characters "xl-" are contained within Cell D1, the formula will return "4"
View 5 Replies
View Related
Jan 30, 2009
I'm trying to extract pieces of a part number and place each section in another cell. It's kind of like a text to columns, but in a formula and removing part of the data. This is what I'm starting with: Cell 3A: HB149E.505
I'm trying to figure out a formula that will fill the following which are currently blank:
Cell 3A: HB149
Cell 3B: .505
Cell 3A will always have 5 digits prior to the decimal and 3 digits afterward so that is not a problem.
View 2 Replies
View Related
Jun 25, 2009
Need formula to extract the text from a cell, with which a vlookup will be preformed on? For example if a cell contains: 116PH(604052) I would need to perform a vlookup on the PH.
The problem is that the "PH" in this case can vary where it's at in the string of numbers. Also, some cells may only have a three digit number with the two letters following. See my attached spreadsheet as an example. The current formula I have in column A on the attached spreadsheet performs the vlookup fine if there is only the three digit number followed by the two letters, but as I've outlined above, this isn't sufficient.
View 4 Replies
View Related
Jun 17, 2008
I receive a workbook on a monthly basis and it arrives in csv format. I need to format it and split it into several pages prior to sending to managers. I'm comfortable with the vba for formatting and splitting it into several pages but I have a problem with some cells that always have data in them in the same (incorrect) fashion.
how to go about fixing these cells and then I can get on with writing that into the macro.
Currently looks like:
A || B || C
No requested || No Supplied || Percentage
4 || 4100%
12 || 875%
6 || 350%
Should look like:
A || B || C
No requested || No Supplied || Percentage
4 || 4 || 100%
12 || 8 || 75%
6 || 3 || 50%
I was thinking of some kind of Left() formula to extract the leftmost character and place it into it's new cell BUT realise that sometimes I might need to extract 2 (or perhaps even more) characters.
View 14 Replies
View Related
May 7, 2009
I'm trying to extract information from one specific cell, and place it into another. This other thread kind of does what I'm looking for. I wish to extract from the following; "20/04/2009 5:42:51 AM". Placing the date in one cell, and the time in another. All spacing within the quotation marks is respective of the cell. Obviously dates and times would vary.
View 4 Replies
View Related
Feb 24, 2009
I have a cell containing text and numbers not in same order.. how do I extract only the number out into a cell.
View 14 Replies
View Related
Jul 4, 2009
i have in a cell a Text like this..
Chicago (TZGUG)
i tried a formula.. like this..
=LEFT(F3;LEN(F3)-1
to remove only ) ..
but how can i remove the ( too ..
and also Chicago
so the result in a cell would be TZGUG
View 11 Replies
View Related
Dec 21, 2011
I currently have an issue with sorting data that i download into excel.
I have a list of rows that have text that look something like the below:
redeem: 141BA9835E Colour: Silver
I want to copy only the colour at the end of each of the colours into a new cell. So in theory the formula will pick only the colour out. Below is a list of more rows as an example where it changes slightly:
redeem: C3CC237E3C Colour: Silver
redeem: db56b2177c Colour: Pink
redeem: 7d3fe7eba2 Colour: Black
redeem: EBE56BDA7A Colour: Silver
To summarise have can i extract into the cell next door, only the colours from text
Currently i am having to manually type only the colour into a cell, and it is taking forever when you have 1000's or rows
View 9 Replies
View Related
Oct 5, 2012
I have a column with numbers in. The column has been formatted to include a currency code but this can change on each row, e.g. M1 may be £20,000,000, M2 may be $40,000,000.
I want to insert a column prior to this column and extract the currency so that in L1 I would have £ and in L2 I would have $.
View 2 Replies
View Related
Jul 24, 2013
I have a bunch of contributions events and I am trying to extract the date ... for example. each line has "contribution 11/02/2011" or "contribution 12/17/2010" is there a formula that will pull out the date. this way I don't have to do it manually.
View 1 Replies
View Related
Mar 4, 2014
Cell A3 has the formula: "=MOD(H16-H15,1)"
Cells H16 and H15 are time entries
If H15 = 12:00pm and H16 = 2:00pm, the result in A3 = 2:00
Cell A5 has the formula: "=A4*A3"
In cell A4, I enter a number, say "4", which produces the result "8" in cell A5
Now...I want to add to the formula in cell A5 to make it "=(A4*A3)+A4". The result I WANT in this example should be 12 but no matter what, the answer always ignores the "+A4" part and always just gives me A4*A3.
So the question is, how do I extract the result "8" from cell A5 as a number to which I can add (or subtract...or ANYTHING) other numbers accurately?
View 3 Replies
View Related
Sep 3, 2008
Does anyone know of a formula that will take an expression such as
"C1-5" in a cell, and produce five separate values in 5 cells:
c1
c2
c3
c4
c5
Even a starter would help, like how to extract values from "1-5"
Ultimately I'd like to use this for values that can have up to 3 digits following the letter (ie, C99-112.)
View 9 Replies
View Related
Nov 18, 2008
I have a cell that has address and city with postal code.
I need to be able to extract the city and postal code on its own.
I know i could do Data - Text to columns but i would like to keep the cell intact and not have to split it up.
Here is an example of the cell in question:
1234 Parc street, Montreal H1A 2N2
View 9 Replies
View Related
Dec 2, 2008
I have a downloaded file from a bank that has a list of names and I need to extract the sirname. The sirname is always last, however there are blank spaces trailing the name.
Example of list is as follows:
MS L SMITH
MS JOAN STAKE
MS MARGARET LOVE
MS J POOL
MS LOUISE HURT
ELAINE TASSETT
MR MARK BOOTH
MR IRVING CHAN
Using text to columns will not work as sometimes there are 3 parts to the name and sometimes 2.
View 9 Replies
View Related
Sep 2, 2009
I am trying to extract a partial string from a cell. There are 2 lines in each cell seperated by ALT + Enter
Example:
GREAT SOUTHERN ROAD
GUNDAROO QLD 7012
What I want to do is extract only the first line of the text and put it into the next column.
I've tried using the FIND, SUBSTITUTE formula, but because the 1st line of the address varies in length, it seems to cause more problems than it solves.
View 9 Replies
View Related
May 10, 2006
I have written a code using VB for extracting data from notepad to excel and copying the certain data like date in other cell. I need to extract a particular value from the cell where data is copied to another using mid or any other function in VB.The code is as follows:
Private Sub CommandButton1_Click()
Call data
Call compare
End Sub
Sub data()
Dim fs, f, ts, s
Dim r As Long
Dim r2 As Long
Dim e1, e2 As Variant
Dim i As Integer
Const ForReading = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile("C:Documents and SettingsAdministratorMy DocumentssheetalCingularStatus .txt").....................
View 4 Replies
View Related
Sep 1, 2007
i have a cell which contains data like this
in cell A1
12. John Smith (67)
There are hidden spaces in cell a1 as well which are not needed, which i can get rid of using TRIM so that may need taken into consideration ??
In Cell A2 i would just like John Smith
In Cell A3 i would just like the 67
the number and . at beginning is not needed just the name and number (no brackets)
View 9 Replies
View Related
Feb 21, 2008
I have a spread sheet with following:
Name|Address|Home Phone| Cell Phone| Status|Project Name|
I want to select name and extract only home phone and cell phone nos.
Please help me with a code. I have about 2500 entries.
View 14 Replies
View Related
