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 RepliesI have a problem in converting text to columns for a string that has 1000+ entry's. Excel bumps me out after 851 using the Split function.
The data I can only download in a text format has >1000 fields that are seperated by ",". Not a problem so far but when I attempt to convert to columns there is a maximum limitation of 851.
Data1,Data2,Data3,Data4...................Data1000,Data1001,Data1002 etc...
I have tried:
Sub Split_Delimeter()
Dim Words() As String
Words = Split(Cells(2, 4), ",")
MsgBox "String contains " & (UBound(Words) + 1) & " words"
End Sub
I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.
U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date
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.
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
We are doing a database conversion and I have been tasked with converting all our old promotion codes to a new system. The long and the short of it is this:
I am trying to write an equation that will look at two cells (say A1 & B1) which when put together could be one of the following numbers (01,02,03,04,05,06,07,08,09,10,11,12) and translate them to a letter (A,B,C,D,E,F,G,H,J,K,M,N)
So 01=A, 02=B and so on...
I was trying to use if statements, but there are too many.
how to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.
Code:
Function colNumToChar(colNum) As String
Dim Num1, Num2, Num3 As Integer
Dim Let1, Let2, Let3 As String
[Code]....
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.
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?
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)?
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).
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
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 RelatedI 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.
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.
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.
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 RelatedI 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
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
I was wondering if it was possible to do something like this:
x = 15
test = "x"
MsgBox (test)
with the message box saying "15" instead of "x". I guess more specifically what I want is:
x = 15
test = Range("A5").Value 'Range("A5").value = "x + 2"
MsgBox (test) 'I want "17" to be outputted
Is this possible? Somehow convert the string test into an integer and read the variable?
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.
I'm currently trying to copy a dynamic range based on the row number of a selected listbox item. I'm able to get the row number of the selected listbox item from the data tab in my file. I, however, am stuck when it comes to copying a fixed column / varying row range from this tab and copying it over to another tab.
Private Sub CommandButton2_Click()
Dim ID As Long
Dim Row As Long
Dim Cnt As Long
Dim SearchTermsStr As String
Dim SearchTermsRng As Range
[Code] ....
SearchTermsRng is returing "Nothing" but the SearchTermsStr returns "F3:O3"
Is there any way in VBA to convert a Variant into a String? This code doesn't seem to be valid:
Dim vName As Variant
Dim sName As String
vName = "John"
sName = CStr(vName)
I am trying to check my sheet names to see if they convert to a valid date format. If it doesn't, then I want it to check for the next criteria (ex - if it equals "Page2"). With the code below I keep getting an error of type mismatch. Code is below.
[Code] ......
I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.
Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?
I have a spreadsheet that has 3 different dates in 3 different cells:
B4 - 3/24/2014
B5 - 4/6/2014
B6 - 4/14/2014
From that information I need once cell to display "Mar 24 - Apr 6 paid Apr 14"
Reason being is that I am importing journal entries into an accounting program and I am allowed one cell each row for the description. This would let me change the dates only and my descriptions will fill out before import. It is important that the months are abbreviated to 3 letters as I am limited on how many characters the description can be.
I have a function that will be used across 10K+ records at a time, so I was taking a look at optimizing it a bit. At the start of the routing I have a string with a 9 to 15 digit numeric value in it. I wanted to load each individual digit into a byte array. I started out using:
byVal = StrConv(sVal,vbFromUnicode)
But that actually loads the ASCII Character codes into the array not the digits themselves. From there I considered looping through the array applying CByte(Chr$(byVal(i))) but then I wondered if it would make more sense to just load the array with Mid$(sVal,i,1)... Does anyone have tested knowledge on which performs better? Is there an option C I have missed?
I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:
Simple example of the problem:
Sub test()
Dim testy As String
testy = Now
'testy will equal 04/10/2007 17:20
Range("A1").Value = testy
'A1 will equal 10/04/2007 17:20
End Sub
Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.
I have an ACD program that gives me a time in "mm:ss" format, or in ":ss" fomat. I have 2-3 problems with charting this data.
1 I am inheriting the production of charts from someone else, with old data being displayed by seconds only I am supposed to keep this display.
2 the data that is in mm:ss format is recognized as being in full date format, for example 2:14 is recognized as 2:14:00 AM instead of 2 minutes, 14 seconds.
3 the data that is in :ss, ie :44, is recognized as a string.
Any thoughts on how I can convert both cases to a integer seconds? I know for example that I could mid() the second condition, but I am stuck thinking about the first condition.
I can't adjust the way the time comes in, unfortunately. It is an auto-generated export of the data from a switch.
I have a userform that reads in a list of names into a combobox. When you choose a name from the combobox it then shows the that persons details from a sheet containing all the names.
I have some text boxes that allows the data to be modified and returns it to the sheet which works fine. What i also want it to do it put the data from the form into a sheet for that individual. For example the name of Ford would go into the Ford worksheet.
How do i read in the value from the combobox on the worksheet and use it as a variable to use in my code. For example
Sheets(Value from the combobox).Cells(NoOfRows, 3).Value = Me.TextBoxChangePosistion.Value
*NoOfROws is the variable to find a blank row