Append All Digits Before Character To All Numbers Between The Same Character
May 18, 2008
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
View 5 Replies
ADVERTISEMENT
Nov 27, 2012
To all sifus out there, how can i transfer from these:
NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182
To these:
NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182
View 1 Replies
View Related
Sep 20, 2009
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
View 9 Replies
View Related
Aug 7, 2009
I currently have: ...
View 8 Replies
View Related
Jul 24, 2007
Say for example I have ABCD-ABC12 basically an arbitrary length of alpha (A-Z) characters followed by an hypen "-" followed by another arbitrary length of alpha (A-Z) characters and then
immediately followed by an arbitrary length of numbers. (with no spaces between alpha and number)
How can I extract just the numbers from the group of alphanumberic characters after the hyphen
and set it to a LONG variable?
View 6 Replies
View Related
Dec 4, 2012
Here is an example
Column A: Column B:
Row 1: Walmart 7886 1540.12 1540.12
Row 2: Walgreens 7886 37.95 ?
Row 3: Exxon Moble 7886 86.37 ?
I need to extract the number (regardless of the amount of characters) signified by the "." Another words, I need a formula that will give the the answers of the following in column "B"
1540.12
37.95
86.37
These are the amounts spent. It would be easier if there was a "$" but theres not... so I need to find the "." and give me the numbers associated with it.
View 5 Replies
View Related
May 14, 2008
I want to change the character ~ with . in order to be able to make them numeric values to be feeded to other functions. But REPLACE seems not doing the job so I've been checking out other options such as seperating after and before the character ~. Details are below. I've been trying to use this formula to extract values from a delimited database which I open with excel. The formula that has brought me close is =IF(ISNUMBER(E51)=FALSE,LEFT(E51,LEN(E51)- FIND("~",E51)),E51)
14010~000
3210~0000
When I import the database, the figures above have originals as 14010.00000 & 3210.00000 but transfer to excel as above. As far as I have observed 9 character spaces are displayed & the DOT transfers to ~ for some reason. I need the LEFT section of the ~
View 4 Replies
View Related
Feb 10, 2014
I have a field that contains rows of data, each made up of a Number followed by Text. The length of the numbers differ - sometimes 4 digits, sometimes 5 or 6. Similarly, the text differs by word-count. An example is
2546 Nags Head
75698 Dog & Duck
634 Crown
I want a way of dividing the numbers from the text (numbers in one column and text in another).
Standard 'Text to Columns' won't work: I can't use 'Fixed Width' due to the number-length varying, and I can't use 'Delimited' and [space] as it will then split all the text up word-for-word (concatenating them back will take ages as my list is 480 rows long).
View 5 Replies
View Related
Mar 3, 2008
I have a spreadsheet with a column that containts UNSPSC codes that can be in any of the four following formats:-
10
77/11
78/10/22
44/12/19/04
I basically want ta formula that will find and remove any / leaving me with
7711
781222
44121904
View 9 Replies
View Related
May 6, 2008
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.
Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}
I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?
My formulaic solution is thus: .....
View 5 Replies
View Related
Apr 30, 2014
I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.
=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)
View 12 Replies
View Related
Jul 27, 2009
I would like to create an If Then statement in which the condition is based on if a string is a simple text or a formula. Some of my cells contain a formula and some contain simple text...and I would like to cycle through all of them- only do something different to them based on what the cell contains.
View 18 Replies
View Related
Mar 6, 2009
I have some cells with some spaces and then text where I want to find the position of the first letter. Do any of you know about any function I can use?
The example below contain 5 spaces and then my name. It should give me the answer 6.
" Magne"
View 8 Replies
View Related
Nov 10, 2009
I want to add a letter to the end of value and have it start over at "A" when the cell value changes. For example
My Column contains
BIR1510
BIR1522
BIR1524
BIR1524
BIR1524
BIR1524
BIR1532
BIR1532
BIR1533
I want this in another column
BIR1510A
BIR1522A
BIR1524A
BIR1524B
BIR1524C
BIR1524D
BIR1532A
BIR1532B
BIR1533A
View 6 Replies
View Related
Dec 10, 2009
i have a column that is 1900 long and i need to add a "comma" to the end of everything in each cell in that column. How do i go about this? so at the moment it look like this:
random1
random2
random3
random4
But i need to look like this:
random1,
random2,
random3,
random4,
View 3 Replies
View Related
Dec 17, 2008
A list of URL's in each row. Here's an example:
webmd.com/diet/rapid-weight-loss
webmd.com/food-recipes/features/10-everyday-super-foods?page=2
webmd.com/news/20000529/over-the-counter-drug-works-to-suppress-appetite
weightdepot.com/
weightloss.about.com/
weightloss.about.com/od/easyweightlosstips/
weightloss.about.com/od/southbeachdiet/a/aa060705a.htm
weightloss.com.au/
Here's what I'd like to accomplish: For each row that contains a URL that ends with a / I would like to remove it. If I use the find/replace feature, it would remove all /'s, which I don't want since a good majority of the URL's contain the /'s in various parts of the URL, such as in the example above. I'd also like to accomplish adding a / to the end of every URL for a list that does not have the /'s at the end.
View 5 Replies
View Related
Jan 9, 2009
I'm looking to create a macro that will take a input from extract master.xls workbook and search that contain in the transaction.xls workbook.
I need not want to search the entire row exactly, instead if the search string found any where in that row, that search string alone need to be removed from that row.
For example.
Search String (File : extract master.xls, Column : TAG) :- ....
View 6 Replies
View Related
Nov 18, 2008
I'm adding some necessary code to the beginning and end of some cell contents in Format | Cells | Custom and I seem to be coming up against a character limit.
I'm using the phrase "!!<"@">!!" to add the code, but with some cell contents the result is ########### (but much longer), and I'm having to remove some of the text to make it work. The limit seems to be around 255 characters.
View 2 Replies
View Related
Feb 9, 2012
Is there a way to do a left formula where you can have it pull the whole cell except the last character? Basically, I have a sentence that looks like this:
apples, oranges, pears,
And I want to drop that last comma. But the length of the each cell varies from row to row.
View 3 Replies
View Related
Jun 17, 2013
I'm trying to find a formula to add a "Y" to the beginning of a value less than 7 characters and and not to add a "Y" to the beginning value that is equal or greater than 8 characters.
This is how I need it to look.
Y123
Y1234
Y12345
Y123456
Y1234567
12345678
View 8 Replies
View Related
Mar 4, 2007
I copied this from Excel:
Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
I have an Address Book from an old Personal Information Manager that exports the file as ASCII.
My problem is when I view the .asc file in Excel, Excel has created two rows for several entries.
That creates a problem as I wanted to select Column "A" then do Text to columns with a comma delimiter (the asc files has the comma in place)
However, since Excel put in the second line, I'll not be able to put in a header column.
When I look at the .asc file there is no way that there is more than 32,767 characters in each row. So why did Excel split it up? ....
View 9 Replies
View Related
May 23, 2008
I've searched for this in the forums but can't find anything. I need to find/replace a character in Excel, it's a question mark in a box and looks like this
I've tried searching for special characters (using the ALT key and number pad) but I can't find anything matching this.
When I save the spreadsheet as a CSV (TAB delimited) this character gets picked up as a TAB, but when I try find replace on the TAB character it doesn't work!
I'm using Excel 2007.
View 9 Replies
View Related
Nov 6, 2008
I have a whole bunch of dates in a column that look like this:
5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.
*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.
View 9 Replies
View Related
Mar 27, 2009
do you know any function that recognises which character is in a particular cell?
View 9 Replies
View Related
Jun 27, 2006
I'm looking for some VBA code that would look at a column of data, look at each cell value in that column and if the cell value starts with the letter 'G' and the 5th character in that cell is not 'Z' or 'X' I would like to highlight that cell. Example:G123-123 would be highlighted whereas G123Z-123 would not.
View 4 Replies
View Related
Jan 3, 2007
What formula should I use to search for cells which have a certain single letter or digit.
Ex. Those with the letter O
2007-FHYO-45 YES
2006-FHY-45 NO
2007-FYY-38 NO
2007-FYY-21 NO
2007-FGHO-81 YES
View 3 Replies
View Related
Jan 3, 2007
I would like to get the nth character from a string. For example, let's say there is a string "jupiter", and I would like to get the 3rd character of this string, "p". Is there a function in excel vba that would allow me to do this?
View 9 Replies
View Related
Jan 9, 2007
I am new to Excel and just starting with formulas. I am wondering if a formula can handle the following:
We have a column that has a url in each cell. Example (I removed the http:// before it so that all the words show up and it doesn't change to a clickable link):
i25.photobucket.com/albums/c54/bigbusy/S_Images/sylvantropicalbirdi.jpg[/url]
We would like to tell it to copy the portion after the last forward slash and until the period and put it in another cell. In the above example it would give us sylvantropicalbirdi. We then would want to append a _t to it.
Is this possible or am I just wasting my time trying to figure it out?
View 9 Replies
View Related
Jul 7, 2007
I need to replace the sixth character in each row of a column within an excel spreadsheet and wanted to know how to do this within vba and if anybody had any example code
View 5 Replies
View Related
Oct 9, 2007
I'm trying to go through a short list of letters and change the contents of every cell in the list that contains a "c" into a "M". I've written this code, but I get a #Value! message on my spreadsheet when I run it:
Function FindCpmDpm(SearchRng As Range)
Dim CelRng As Range
For Each CelRng In SearchRng
If CelRng.Value = "c" Then
CelRng.Value = "M"
End If
Next CelRng
End Function
I am having trouble accessing the cell which contains the "c".
View 9 Replies
View Related
