String Extraction Where Two Letters Exist

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


ADVERTISEMENT

Removing Two Letters From A String Of Letters And Numbers

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

String Extraction

Mar 24, 2009

In cell A1 I have the text
20. Black Angel Blues (Sweet Black Angel) - Nighthawk, Robert

In cell B2 I would like all of the text from the period/full stop after '20' until the - hyphen character

In cell C2 I would like the text after the hypen, but if as in this case there is a surname followed by a first name I would like them rearranged so that the full name is shown first name followed surname without a comma.

The end result in this example would be:

B2
Black Angel Blues (Sweet Black Angel)

C2
Robert Nighthawk

View 9 Replies View Related

Extraction Of Number And String From Cell?

Dec 24, 2011

Examples of content of two cells are:

50.20283625183448da
1195.368480208458hr

where the right alpha characters always count two.

I need the left number as a Double and the right two characters as String (or ?) to compare with Array("mn","da","yr") etc to determine the index I don't know how to write the code.

View 2 Replies View Related

VBA Partial String Extraction From Variable?

Feb 12, 2013

I have a comment that I'm putting into a variable for the purpose of obtaining the persons name.

Cell C3 (in this example) just contains their employee number, but it's comment contains...

Tech ID: 123456
Name: John Doe

The Tech ID is variable from 3 chars to 6 chars and obviously the name is size is also variable. The end result will be a variable with just the persons first and last name extracted from the comment.

Code:
Dim mycmt As String
Dim techname As String
mycmt = Sheets("tech").Cells(3, "C").Comment.Text
techname = Right(mycmt, BIG-OLE-BALL-OF-CONFUSION)
MsgBox techname

My 2nd question. This is the 3rd time in recent days that I've needed a partial string and Mid, Left, Right, Len, Find just boggles my mind.

View 6 Replies View Related

SaveAs With String Extraction For File Name

Jun 16, 2014

I am trying to use the string (sPath), I previously define to preform a saveas function after the script runs.

Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant

Application.EnableEvents = False
Application.ScreenUpdating = False

[Code] .......

These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.

So as an example an excel workbook report generated in the folder:

"D:DropboxWorkVerification Reports!Test"

Would be saved as "!AirCheck AutoTest Report - !Test.xls"

or a report in the folder:

"D:DropboxWorkVerification ReportsHoliday Inn Surfside"

would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"

Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".

View 7 Replies View Related

String Extract - Separate First Two Or Three Letters In A String

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

Change Letters Within String

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

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 View Related

Converting A String Of Letters In A Row Into Columns

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

Remove Letters And Zeros From String

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

Changing Letters In A Text String

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

Remove Numbers - Leave Letters In A String?

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

Separate String Of Text Containing Letters And Numbers

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

Wrong Letters Or Numbers In A Text String

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

Extracting Letters And Numbers In A Random Alphanumeric String

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

Extracting Series Of Mixed Letters And Numbers From String

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

Looking To Extract Numeric Value With Two Letters Attached To It From A String Of Text

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

Extract First Character Of Each Word In String To Create Code Letters

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

Separate Letters And Numbers In A String Into Separate Columns

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

Macro For Creating Letters To UPPER Letters

Dec 7, 2009

I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.

Excel forum to EXCEL FORUM

View 5 Replies View Related

Remove Letters From A Column Containing Both Numbers And Letters

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

How To Seperate Small Letters And Big Letters

Mar 9, 2009

how can I seperate small and big letters from one collumn, example:

NAME
name
NAME
NAME
name
name

View 11 Replies View Related

Column Letters- VBA Command To Get The Letters

Nov 17, 2009

Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?

I have to letter a list whihc means setting up a loop using character codes.

I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"

of course the easiest would be to pick up a column value as a letter

NT values do not get a number

A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####

i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.

View 11 Replies View Related

Sheet Name Extraction

Jul 6, 2009

can anybody tell me how the get the name of a sheet as a string?

i have a formula using the name of a sheet and i dont want to leave it hardcoded.

ex:

=IF(ISERROR(MATCH(_02.07.2009!B6;(OFFSET(_01.07.2009!$B$5;0;0;COUNTA(_01.07.2009!$B:$B)-1;1));0));1;0)

i need that dates as variables. those are the names of the sheets im using.

View 10 Replies View Related

Data Extraction

Mar 11, 2008

I have an excel file with a large list of data in the following format, all the data is in the A column –

Mar 11: category one – process number one (1)
Mar 11: category two – process number two (2)
Mar 11: category three – process number three (3)
Mar 11: category four – process number four (4)
Mar 11: category five – process number five (5)

I would like to extract from the list, ONLY the data highlighted in red i.e. the process number data –

Mar 11: category one – process number one (1)
Mar 11: category two – process number two (2)
Mar 11: category three – process number three (3)
Mar 11: category four – process number four (4)
Mar 11: category five – process number five (5)

View 9 Replies View Related

Value Extraction To A Cell

Aug 3, 2006

I have the following code which I know is wrong:

Public Sub WeeksOfSupplyatInitial()

'get the Instore Date Week add 2 weeks, then extract from Weeks of Supply
'and return the value to cell D4

Dim result As Double
Dim firstRowTotal As Double

View 3 Replies View Related

Workbook Data Extraction

Oct 23, 2008

I have a folder on my drive that holds excel files, some of which I would like to extract data from, using an excel workbook outside the folder.

Can you assist me in achieiving this action? Or, direct me to literature that will have the answer?

View 6 Replies View Related

Data Range Extraction

Feb 18, 2014

I have a table similar to this

L M N O P Q
title1 title2 title3 0
12 5 0
0 23 0
13 1 0
453 1 0

I need a formula to get the following structure (without using macro):

A B C
title1 title2 title3
12 5 0
0 23 0
13 1 0
453 1 0

Attached File : ReturnNonblanks.xlsx‎

View 6 Replies View Related

Column Extraction Using Macro?

May 13, 2014

My column extraction requirements are:

Using the same file you posted we just need column list; when we press ctrl+z the box should come up and have only columns list showing up and buttons like "select all", reset, and cancel should be present.

In this column list all the column headers should be shown under the column list and the interface box(ctrl+z) should be movable like you have already done in the last post

The other condition is that if any new columns gets added anywhere in the first row of the sheet the user interface(ctrl+z box) should show it in column list so when selected it can be extracted to the output file.

In short every thing is similar to the previous file samplecopy v6.1.xlsm you posted but this time only column list is required and all the column headers under it and remove the note which is applicable to previous query.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved