How to remove a "-" out of first two letters of a sentence. if there is a "-"
for example:
BEFORE: Red-Blue Shirt With Ribbon
AFTER: Red Blue Shirt With Ribbon
However, if there is not a "-" in the first two letters then it not change anything.
For example --nothing changes:
BEFORE: Red Blue Shirt With Ribbon
AFTER: Red Blue Shirt With Ribbon
I have some rows that have some text for example "0- 56933 tex". the numbers are not always the same, but there is a dash, and sometimes there is no space after the dash. I want get the numbers before the dash in a variable and the one after in another variable.
View 9 Replies View RelatedI 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.
I can use find and replace to remove the following, however as I would be doing the same process numerous times it would be very tedious.
I would like 2 VBA scripts.
1 - replace the following letters (uppercase & lower) in Column A and C replace A, V, N, Z, X, Q
so if it finds A it would remove A from the cell.
The cells may contain other letters including these one's i.e
A123XCB235 or
123VBN7779A
2 - The second independent code would replace the characters replace A, V, N, Z, X, Q with P, M, L, R, J, H ie. A becomes P, V becomes M etc...
Again the cells may contain other letters including these one's i.e
A123XCB235 or
123VBN7779A
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'm having trouble with separate a text string.
The first part of the text is in all uppercases and the latter part is in lower cases as seen below.
PLAVIX BLOOD THINNER RXNo Title Assigned - #588303
I have to separate PLAVIX BLOOD THINNER RX and No Title Assigned - #588303
into 2 separate columns from that one column.
Is there a faster way of doing that than doing it manually?
Is there an If statement for that?
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.
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 have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
how can I seperate small and big letters from one collumn, example:
NAME
name
NAME
NAME
name
name
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.
I work in the Oil and Gas industry and deal with large data tables that have "Location Codes" for lack of a better term.
The problem is that not all Databases use exactly the same format of code, they will however always have the following:
xx-xx-xx-xx ---> ie. a string of four numbers each seperated by a Dash.
This string sometimes will appear after a text name of varying length. With this in mind I would like to parse the four numbers into four different columns.
Things I've tried with some success:
Using the "Right" function to break off the location code off the end of the entire name, and then using the ".parse" command in VB to seperate the numbers at given intervals.
This is great for certain purposes however because the four numbers are not always 2 digits, and the ".parse" command cuts at given intervals it is hard to accurately parse an entire set of data.
Some examples of location Codes are given below:
9-23-78-11
WEST DOE 11-16-81-15
KNOPCIK 3-9-73-9
[URL]...
This entire link is in one cell i would like the data Iowa_Health_System
What formula can i use
I have a spreadsheet that shows a dash in some blank cells and I can't get rid of it. Cells are formatted as general, there's no event code, no conditional formatting, no data validation, no auto-formatting, no nothing. I can copy a cell from outside the range with no dash in it and paste it into one of the cells with the dash and the dash remains. I can't figure it out.
View 9 Replies View RelatedI recently saw Krishnakumar use the formula.
= sumproduct(--(a2:a10=d2),--(b2:b10=e2),c2:c10)
I haven't seen sumproduct used this way. Very cool. I see how it is working, but can anyone tell me more about the syntax of the dash dash used in array 1 and array 2? I have never seen this. How is it different than =SUMPRODUCT(1*(A1:A9=G1),1*(B1:B9=H1),C1:C9), which is the syntax I'm used to seeing.
I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.
The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.
My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?
Below is a strip down version of the code.
VB:
Sub test()[COLOR=#333333]
[/COLOR] Dim rLookInADR As Range
Dim foundcell As Range
Set rLookInADR = Range("b1:b380")
Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
MsgBox (foundcell.Row)
End Sub
[COLOR=#333333][/COLOR]
Here is what my worksheets looks like.
54841
54841-1
54841-2
54841-3
55654
58850
58850-1
53254
53254-1
53254-2
56696
56696-3
56696-4
53214
54789
So this list of information needs to be changed to the following
54841
54841-1
54841-2
54841-3
55654
58850
58850-1
53254
53254-1
53254-2
56696
56696-3
56696-4
53214
54789
All the numbers in red need to be cleared. No I have been able to find the original number and clear it. For example I can have the program find 53254 and clear it, but I am really having a tough time having it find -1, -2, -3 and so on. If the program finds a -1, I need any dashes after that to be cleared. If the program doesn't find a -1, then it goes to -2. If it finds a -2 then any number after that needs to be cleared. Here is the code I have to clear some of the numbers.
I know this works because I have seen it in a workbook at a client's office.
I need a custon number format to substiture an "em" dash for zero. An em dash is a hyphen with the width a the letter m. There is also the so called en dash tht is the width the the letter n. A search of the internet shows that to create an em dash in a cell that contains a 0 ("zero"), hold down the alt key and type 0151 on the numeric keypad. Typing 0150 insert a en dash.
I want to be able to set this up as a custom number format in the custom number dialog.
I have entries that look like this - 300-7188258-1
300-8983980-5
I need to remove the dashes. I find and replace all - with nothing. that has worked for YEARS. Seems simple to me.
Today, when I do it, Excel leaves the "300" and removes everything else. I even did a CLEAN function and it still does it.
i am trying to isolate everthing after the last dash, now i did the same with the first dash using left, i tried to do the same using Right to get "155", but it gives me incorrect result,
Sheet1 ABC887878-1554-155 87878987878-1554-155 554-155Spreadsheet FormulasCellFormulaC8=LEFT(A8,FIND("-",A8)-1)C9=RIGHT(A9,FIND("-",A9)+1) Excel tables to the web >> Excel Jeanie HTML 4
In excel if you press F5Special, you will be given to select cells with some criteria but there is no criteria to select cells with zero value in a selected range. Is any such option is available in excel or a macro is needed for the same?
View 9 Replies View RelatedI am trying to create a simple dash board using only Excel and no VBA. What I am trying to do is I want to create dependent drop downs and link up drops downs that will pull out the required data individually or with combination of those two. For example, let's say
A column has Dates of transaction,
B column has Item name
C column has Units
D column has Month name
E column has Shifts (Morning or Evening)
I am trying to make a dash board that will pull total Sum of individual items based on
Only Date wise
Datewise + shift wise
Month wise
monthwise + shift wise
I managed to create drop down list for dates, months, and shifts but i dont know the way how to link the individual drop down lists together to pull the data based on the selection. which function i should use here and how?
I want to know how many emails are in my list. There is dash sign where the emal id is not available. The subtotal formula gives me the total entries in range but I want to know only email count numbers.
In my range there are 6 entries and only 4 email ids. Now I'm getting output 6 but I want it 4 ignoring 2 dash signs.
I need a formula that will extract only the text between the 1st and 2nd dash in a cell. Example:
10466193-1909457-
10648145-3026718-t526363536m
This formula would return only the following:
1909457
3026718
I have a row of 2900 single letter (middle initals) however 222 users have no middle inital. this is a password scheme and need 7 digits, without the middle inital i only have 6. so I want to replace all 222 cell that are blank with a dash can this be done without doing each by hand?
View 4 Replies View RelatedWe often get spreadsheets from our customer that are formatted with both comma and dash combinations. We would like to only have the comma seperation.
Example:
R1, R2, R3-R5, R30
Result:
R1, R2, R3, R4, R5, R30
There is usually only one alpha character but sometimes more. Example: CR1, CR2, CR3-CR5.
The following macro works great if there are no alpha characters. how to solve the alpha/numeric combination?
Function Nums(rng As Range) As String
Dim adnum As Integer, n As Integer, num, Txt As String
num = Split(rng, ",")
[Code]....
I am looking to replace the space in between numbers and letters in a text string. The number of entries varies with each row.
Example of the entries in the column I need to reformat. Each entry is in a new row.
Code:
10 SSS
24 MNL
17 HLG 18 LTN
17 CBG 17 HLG 15 HIL 15 thn 22 ALV
17 ELP 34 HLG 15 HIL18 THN 10 TTL
What I am looking to achieve:
Code:
10-SSS
24-MNL
17-HLG 18-LTN
17-CBG 17-HLG 15-HIL 15-thn 22-ALV
17-ELP 34-HLG 15-HIL 18-THN 10-TTL
How to make auto numbering with mark "-" combination and result placing in D column, like this below ;
row/line
col.c
col.d
col.e
15
john
-
xxxx
[Code] ........
I am importing a .txt file into Excel that has two columns of dollars and cents. When I do my subtotals, all subtotals should be zero. Some of the totals show as 0.00, some as (0.00) and some as a dash which is what I would like for all of them. I have tried: ...
View 9 Replies View RelatedI have a macro already that prints my blank invoices sequentially. What I would now like to do, is insert a dash. SO instead of just the invoice number, I would like to have a 1- in front of each number;
1-1
1-2
1-3
1-4 ... etc.
I have uploaded the 'invoice' that I am currently using.