I need to insert 00 if the string of text = 13 in a specific column.
eg. Column K can contain any amount of lines of data. The data will either be 15 or 13 characters in length. If its length is 15 its fine, if its 13 I need to get it to 15 characters.
The first 6 characters are letters so if the length is only 13 I need to add the 00 in after the last letter.
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
i have a spreadsheet system that imports data from an .mdb including a memo box, but the limit on excels displayed and printed data (1024 characters i think) is causing issues!
is anyone aware of a method using VBA that i can use to ensure i can see all the text that is in the memo box not just the 1024(?) characters?
I'm trying to sort by a column with one to four digit numbers, any of which may be followed by a single letter, i.e. 1,2,3 4A,5, 10,11A,75,101A,600,705,1010B,1011A.
I'd like them to sort in the above order. I am only able to have excel 2003 sort all of the numeric cells first, followed by the cells with the alpha character.
Ive written the macro below to sort Column A according to the length of the cell values in Ascending order. Ive done this by writing the length to Column B for each value and then sorting on column B. Surely there is a more elegant method, perhaps using arrays.
Sub SortIt()
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Value = Len(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub
I am trying to create a string of text that grabs info from other cells, which is easy so for example
=A1&A2&A3
would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.
Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this
At the moment
01MAIN-HELLO
01DESSERT-HELLO WORLD
Would like
01MAINXXX-HELLO
01DESSERT-HELLO WORLD
This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.
I have a row of 1's and blanks, where there might be 10 consecutive 1's, followed by four consecutive blanks. I want to calculate the average length of a string of 1's in the row -- how this might be accomplished?
View 7 Replies View RelatedI have recently purchased a new GPS for my fishing boat. I am trying to transfer my old coordinates from one unit to the other. I am using a .csv file to achieve this. Can some kind please let me know (and possibly tell me) if there is way to insert characters and spaces in to multiple cells. I have over 800 individual coordinates to modify to the new gps and this would take a long time to achieve individually.
I need to replace this: 12.34.567 to this: 12 34.567 N
and this: -1.23456 to this: 001 23.456 W
i just wanna extract the date shown(last data until meet the first comma) in first column to another column.
column 1
abcde, 1234
abcd, 12345
abcdef, 123
column 2
1234
12345
123
I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.
DateNameItem
1012009BobKeyboard
1012009SidMouse
1012009DaveMonitor
1012009EdMonitor
1012009AndrewKeyboard
1012009DanKeyboard
1012009BruceKeyboard
What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen?
I have an ODBC connection to a db2 database in MSQuery. I want to return the length of a string. I've tried Len(string) but it says its not in the library.
View 2 Replies View RelatedThe following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.
Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub
As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.
However, when I pass it to the following function in a module it outputs 2 as the length.
Function FormatTimeValue(vTarget As Integer) As String
Dim TimeStr As String
If IsNumeric(vTarget) Then
MsgBox Len(vTarget) '<= outputs 2
Select Case Len(vTarget)
Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"
I have looked into the maximum length of a variant/string in vba and it appears to be 250 characters. I am running a macro which first lists all excel files in a folder, returning them to a sheet, then using a loop statement opens each one in turn extracting the information to a second summary sheet before closing it. The file path to the folder is ridiculously long and the macro stumbles. I used the =LEN(A1) formula to check if the file names were too long for the string, but the maximum file name length was 226 characters. I've tried both String and Variant to collect the file names but both have the same effect.
View 2 Replies View RelatedI have a column of about 15,000 numbers that I need to update. They are all 6 digits long and I need to insert characters at 3 different points. The numbers currently look like
234567
123456
456789
and need to be changed to
23-45-67
12-34-56
45-67-89
I have the following type of info in A1,A2,A3...
Code:
nameGaryage40cityPittsburgheight190
age30height150
ameLindacityMichigan
citySan Jose
ameHarryheight180age50
My goal is to get as close as possible to this,so it will be easier to sort and manage
Code:
nameGaryage40cityPittsburgheight190
nameLindaage30cityMichiganheight150
nameHarryage50citySan Joseheight180
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one
Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.
For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.
I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)
but it assumes some level of consistency, the "-" in the cell.
I have a set of data in Which in column A is the name of organisation.
If string in col A is longer than 50 I need to split in and put in col B.
That would be simple however I need to do it in a smart manner: i.e. cut it to the nearest full word.
Example:
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
Incorrect; length = 98
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT
Correct; trimmed down to 48.
My question is about formula that can detect spaces and depending on those trim the string down adequatly:
to 50 if 50th char is preceeded by space; if not then check where is the next space going towards left. Once you find it cut the string there.
What formula I can use to get desired effect?
Hey I got a long String like this "[...] increase of x.xx% [...]".
I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.
I tried this formula:
=MID(G14,SEARCH("%",G14)-5,5)
but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.
I have a spreadsheet with 2 sheets. The first just links to another file and pulls in the contents of a particular sheet, cell for cell. The second sheet is a formatted report that I'm creating to put that data into a different format for presenting to others. I'm using the Index and Match function along with range names. All is fine except where the string that should be returned is very long. When that happens, I get "######" all the way across the cell. It doesn't matter how wide I make the column or how tall I make the row, the "#s" do not go away. Is there a limit on the number of characters the Index function can return?
View 5 Replies View RelatedImagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".
Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.
Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.
I have a google earth KML file that I have converted to text, and through a bunch of manipulation have been able to pull a series of GPS coordinate strings into a single cell string. Unfortunately, the string data is beyond what excel 2007 can handle for a single cell. so my thought is to have excel pull each coordinate string into a separate cell with which i can then run a macro to develop a new KML dynamically. (changing multiple attributes based on a query to a database)
Each string of coords actually maps out a single region (path) on the KML, truth be told it is telecom rate center data, and each rate center will have numerous other variables applied to it depending on my company's voice network capability for a given rate center. Currently my only desire is to depict differently any rate centers that I'm able to deliver VOIP services to by showing them in a different color...but these change very often so it will support to be able to auto generate the map from time to time.
the raw data from the KML looks like this:
Code:
CLINTONVL
RATE_CNTRCLINTONVL
STATEPA]]>
ff000000
1
0
1
[Code] ..........
After doing my data import, i extracted via various manipulations, the rate center name (a common lookup value that stays constant across multiple databases), and the string of coordinates. this is where i run into trouble. i need to pull each coord into a separate cell assuming i won't run out of cells in the x coordinates to gather this data OR find a way to grab the data via another lookup to another document...not desireable.
I want my output to look like:
Code:
RC_NAMECOORDSLATAVoip?
ALIQUIPPA
80.334114,40.520974,0-80.327493,40.520023,0-80.323915,40.51957,0-80.31997800000001,
40.519933,0-80.316958,40.521221,0-80.314431,40.521445,0-80.314262,40.521332,0-80.31398,
40.521194,0-80.31315600000001,40.520832,0-80.313057,40.520794,0-80.312805,40.520641,
[Code] ........
First off, my import was jacked up by missing some comma's...this i can fix easily with the string importation and manipulation HOWEVER...i still run into the issue of string length OFTEN.
Is there a way to stop getting the error of a string of poind signd (which look like ################) whenever I'm trying to copy and paste information from another worksheet, or trying to link one sheet to another?
I've already tried resizing the rows and columns to their respective maximums but it doesn't seem to work.
I have a cell (B2) I would like to apply multiple data validations to.
I know I need to use the custom formula option but don't know how to write the formula.
I don't even know if it is possible, but here is what I'm after
I need to make sure the cell is 4 digits long
I need to make sure the cell starts with a zero (Because the cell starts with a zero I have it as a text cell)
I need to make sure the 2nd number is not 0 if A2 begins with 5 (A2 is also a text cell).
Is there any function or methodology to insert text in mid string? ex. I would like to insert a string after the dashes below.
0506-40365503970
0506-40365752746
0506-40365994154
I have a userform with a numerous textboxes, which I have labeled with similar text and a unique number at the end (num1_txt, num2_txt, num3_txt, etc.). I am trying to create a for loop that sequentially references the numbers of the text boxes and pulls the user-entered value in the text boxes into an Excel document.
Here is the code I have so far...
VB:
i = 1
For i = 1 To 18
Dim cyltrack As String
cyltrack = "Me.cyltrack" & i & "_txt.Text"
If Me.cyltrack.Value = "" Then
Cells((cellcount + i), 13).Value = "*"
Else
Cells((cellcount + i), 13).Value = cyltrack
End If
Next i
I am running into problems in trying to convert the string variable, cyltrack, into hard code that I could insert into Me.cyltrack.Value.
I have a list of names in the A column of the sheet. I wish to use the input string, a new name, of a vba box to insert into the column of existing names in alphabetical order by inserting an entire row, which I suspect will be the case anyway. Also 'Return' key doesn't seem to be inserting a CR on the page that I typed this request upon.
View 5 Replies View Relatedi 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")))
I have variable length strings, as shown in the sample cells below
TopographyTAB_FormatSP9200
TopographyTAB_FormatSP9400
OS_DataLandline
ITNTileTabTQ2286
I want to select any record where 5th and 6th character from the right hand end of the string which = either "SP" or "TQ". Where this is the case I'm then looking to truncate the string to remove the last 7 chacters.
None of what I have so far found using mid or right string appears to be particularly applicable.
I'm trying to remove everything after a specific character in a string.
I.e. change a website address to the hostname
http://www.excelforum.com/newthread.php
http://usa.excelforum.com/forum/new
to
excelforum.com
usa.excelforum.com
I'm using this formula, which strips the http:// and the www., but does not replace the characters after the first remaining "/" as the wildcard is not recognized.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""), A2,"http://",""), A2, "/*", "")
Replace characters in a string. I have this macro:
View 2 Replies View Related