Dividing Letters And Numbers String
Jan 25, 2009
A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.
Example:
xxxx1
yyy3yyyy1
xyzxx1yxz4xzx1
xxx2xxx2xxx2xxx2
What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:
xxxx1
yyy3 and yyyy1
xyzxx1 and yxz4 and xzx1
... and so on
Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?
View 6 Replies
ADVERTISEMENT
Jul 29, 2014
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
View 13 Replies
View Related
Mar 18, 2014
I would like to take the below strings and remove any numbers and just leave letters. Or extract the payee from the string. If at all possible, I'd prefer this in a formula.
Card Purchase WAL Wal-Mart Sup 320004 0532 WAL-SAMS CENTRAL LA
Card Purchase WAL-MART #0532 053201 308 N AIRLINE HWY CENTRAL LA
Card Purchase LEBLANC'S FOOD S 874301 209 S. AIRLINE HWY CENTRAL LA[code]....
Basically, (the first line above) I'd like to remove the words "Card Purchase" and any numbers...or preferably just leave "Wal-Mart Sup" instead of the full string. I'd like the product of the first line to match the product of the 2nd line.
View 14 Replies
View Related
Apr 22, 2014
I am in need of a formula that will separate a string of text. Specifically, I want to pull out the number values (including cents) from the rest of the text. This is how the text reads:
10.00 OVERDRAFT PROTECTION TRANSFER FEE
10.00
56.00 ELECTRONIC/ACH DEBIT
56.00
199.00 ELECTRONIC/ACH DEBIT
199.00
2,017.64 ELECTRONIC/ACH DEBIT
2,017.64
The data to the left is the data I am working with and the I want the results to read similar to the results on the right.
View 8 Replies
View Related
Dec 1, 2006
So for a program I am writing, the user inputs a text string of 200+ alphabetic letters. Only 20 of the 26 alphabet can be in this input (excluding BJOUXZ). What I need to have a macro or function do is check to make sure none of these 6 alphabet letters are present in the string, and also check to make sure no numbers are present either.
Also, I would love for the input text to be converted to Uppercase for all letter as it does this check.
View 9 Replies
View Related
Dec 29, 2009
I've got this problem: I need to separate around 40 alphanumerical entry in Column'A' to Columns'B','C','D','E'..
View 6 Replies
View Related
Oct 9, 2013
I have got to extract a series of mixed letters & numbers from a cell. The format of the data i need to extract is always [Letter][Letter][Number][Letter][Letter]][Letter][Number][Number]. The problem I've got is the notes field is not in a standard format as it can be anywhere in the cell.
The table below shows the notes cell & the Data I require.
Notes
Required Data
Fault number AB1ABC12 is complete
AB1ABC12
BC2ABC12 status is unknown
BC2ABC12
pending job ws1abc12
ws1abc12
View 6 Replies
View Related
Nov 20, 2012
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
View 5 Replies
View Related
Jul 24, 2012
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
View 3 Replies
View Related
Apr 6, 2009
I have a total at the bottom of my spreadsheet and I want to distribute that total equaly to the missing columns for each month and each product highlighted in green in the spreadsheet. What formula do I need to use to be able to do that?
At the end I want the numbers in each month to total the total at the bottom.
View 10 Replies
View Related
Sep 6, 2006
I've found how to present time exceeding a minute in seconds ([m]:ss.00), but I need to formulate items per second. Doing simply CellItem / CellTime won't work. I've tried Minute(CellTime)*60+Second(CellTime), but this leaves out the hundreds of a second.
View 3 Replies
View Related
Dec 11, 2009
Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.
Windows XP
Excel 2003
View 9 Replies
View Related
Sep 4, 2012
I am working with flight numbers and want to split out the letters from the digits. Examples,
BA1234A
BA123
EZY4566H
BE7893B
In column A I need the first two or three letters only,
BA
BA
EZY
BE
In column B I need everything to the right of what appears in column A
1234A
123
4566H
893B
etc.
View 7 Replies
View Related
Mar 21, 2013
I'm trying to work out the productivity of something per hour but I'm having a bit of trouble with dividing time in Excel.
Basically I've got
A1 = # of Sales (e.g. 5)
A2 = Total time worked (e.g 07:36:00)
I want C1 to show me how many Sales are made per hour.
View 1 Replies
View Related
Dec 19, 2006
I have a txt file imported into excel that's slightly strange.
But once into excel, I need to edit some numbers.
The values in cell B 23 is 1.
So is the value in B 28, so is the value in B 33, B38, B 43 and so forth ad infinitum... Or so it feels.
I need to edit all those values to 0.
View 9 Replies
View Related
Feb 13, 2012
I am using excel 2007 or 2010.
I need to divide the numbers on sheet 1 column D. This column contains numbers and blank cells so I need to divide by the actual number of cells that contain only numbers. I will be referencing the divided numbers on sheet 2 in a monthly summary format.
View 3 Replies
View Related
Dec 10, 2009
I have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.
e.g.
Dollar # of A B C All Columns
Amount Columns Total
--------------------------------------------------------------------
$25.05 2 $12.53 $12.53 $25.06
$11.47 3 $ 3.82 $ 3.82 $ 3.82 $11.46
$25.05 divided into 2 columns gives $12.53 in Column A and $12.53 in Column B. Total of Columns A and B is $25.06. Over by a penny.
$11.47 divided into 3 columns gives $3.82 in Columns A through C. Total of Columns A through C is $41.46. Under by a penny.
I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".
View 10 Replies
View Related
Jan 21, 2012
Using the following data
R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8
I want it to sort like this:
R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
What is the formula to achieve this?
View 5 Replies
View Related
Jan 27, 2012
I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.
The row may therefore have cells that either have numbers or letters.
Column A will be the title of the row.
I want to write a function to find the average value of the numbers in any row with title 'x'.
So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.
I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.
View 9 Replies
View Related
Apr 10, 2014
I am looking for a while for a macro or a function, which should change needed letters in string into needed ones.
Example:
A1 cell contains words "John Johnson Martinson". I want to switch "n" letter into my specified, lets say "m" and the result should be "Johm Johmson Martimsom".
I would like to get it within range of columnA. Is it possible to get?
View 3 Replies
View Related
Oct 5, 2009
I have a set of letters in a single row DISATAGFOORCBORDQFINFCOBBKIEHDSMCNRSKARDFCJSLCQCECFVSBCBOYKPVYKC. I want to distibute them across 5 columns such that each letter occupies one cell. I might want to specify a different column width later on so the solution should be flexible.
View 5 Replies
View Related
Oct 20, 2011
I have a list of about 1,400 numbers ranging in different lengths. What I'm trying to accomplish with a formula is to remove the single letter at the end of each number (not all numbers have them) and in addition to removing all zeros at the beginning of the number (again not all numbers have them).
Here is an example...say I have the following 5 numbers:
8014554
45678456
87451245
0008014554b
0008014554c
And what I need my formula to do provide is the following results instead:
8014554
45678456
87451245
8014554
8014554
So far I have started with the following =LEFT(A1,LEN(A1)-1), but that will always remove the last character (number and letter).
View 8 Replies
View Related
Nov 13, 2007
I have a list of 3000 strings, all in seperate cells, in a column.
Within each string, are ( say 13 ) letters ( for convience) "A" through to "M" which are of interest to me
The 'letters' of interest occur either once or twice , or none within the individual strings. When a 'letter' of interest occurs within a string , putting a marker in a column on the same row is sufficient.
Using vba, I have extracted the cases where 2 'letters' exist within the strings, by using 3 nested FOR NEXT loops, with an IF test in the middle of
If (InStr(Cells(i, 2).Value, IndexLetter#1) > 0) and (InStr(Cells(i, 2).Value, IndexLetter#2) > 0)Then
and then put my marker in cells(i,3)
where : IndexLetter#1 is set in the outermost of the FOR NEXT loops as one of the 'letters' of interest, and similarly IndexLetter#2 is set on the next inner of the FOR NEXT loop . The inner most FOR NEXT loop ( i ) is the list of 3000 strings
I want to put a marker next to the string , when only ONE of the 'letters' is present in the string AND NONE OF THE OTHERS
View 9 Replies
View Related
Oct 4, 2009
I have text strings consisting of about 500 letters consisting of A, C, G and T. I need to switch A-->T, C-->G, G-->C and T-->A across the whole string. I've tried searching for methods and several techniques without success.
View 9 Replies
View Related
Jul 20, 2008
Looking to extract a numeric value with two letters attached to it from text strings. The text strings vary. Below are the examples I'm looking to extract .51OZ from the first cell, 12CT from the second, and 30CT from the third.
BEANO AD A-G LIQ RS 0.51OZBEANO AD A-G TAB-CHW RS 12CTBEANO AD A-G TAB-CHW RS 30CT
View 9 Replies
View Related
Nov 3, 2012
Junior Fit Softstyle T-Shirt
Antique Cherry Red
Junior Fit Softstyle T-Shirt
Antique Cherry Red
Softstyle T-Shirt
Antique Heliconia
Softstyle T-Shirt
Antique Heliconia
Softstyle T-Shirt
Antique Heliconia
This is column a and b. Looking at b I am looking for a formula that will pull the first capital letter out of each word like ACR to create color codes.
View 1 Replies
View Related
Feb 26, 2014
Tallying entires from a PDF into excel. Such that
c=1000
d=15000
e=50000
f=100000
g=250000
h=500000
i=1000000
j=5000000
k=2500000
l=50000000
In a cell I would like to type =c+d+c for example and have it say 17,000 rather then me having to type in =1000+15000+1000.
View 4 Replies
View Related
Jan 16, 2014
I created this data worksheet with many tabs and multiple users. On the main tab, something weird has happened and I have no clue how it happened or how to fix it....
View 5 Replies
View Related
Apr 17, 2009
This formula will add up the letters D,N,H with one value
Like =SUM(COUNTIF(B4:AZ4,{"D","N","H"}))*12 will =36
How can I add bN =17 bmN =19 bD=24
=SUM(COUNTIF(B4:AZ4,{"bN","bmN","bD"})) to add up to 60
View 9 Replies
View Related
Jul 8, 2014
I'm working on a function that will sum a row of cells containing letters and numbers. I am currently using :
=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:S5,"T",""),"TP",""),"TPE",""),"P",""))+0) as my base.
Right now the formula will work with T, TP, and P; however as soon as I type TPE I get #value.
Either way you get the point of what I am trying to do, however my cells can contain any one of the following combinations:
T,TP,TPE
P,PE,
E
View 9 Replies
View Related
