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"
I am trying to work out how to get nth position of a character in a string. For example in this
Code:
kjishdfiuayigdscka
if I use the formula
Code:
=FIND("i",I$1),
it will give me answer 3. But I want the position of 3rd "i" in the string. The answer should be 12. How can I change this formula to get the correct result?
In cell A1 contains a string, how do I get the number of position of the last numeric character from right to left (this value will be in cell B1).
Example: 12543AR3372C31WWW (In Cell A1)
4 (In Cell B1)
on the web I've found this formula, which returns the postion of the last occurrence of the character in cell B1 in the string in cell A1:
=MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=B1)*ROW(INDIRECT("A1:A"&LEN(A1))))
How do I turn this into a VBA function?
Title should read: Finding the nth Occurrence of a character within a text string
I have a very long text string that is delimited by about 50 "/" to segment certain values within the text string. I want to be able to extract the text between the 33rd and 34th occurrence of "/". How to do this?
Let's say we have a text string in the excel cell: 12.123.12.1
Is there any way to get the latest dot's position using excel's functions?
How to find and show position number? I try merged two function, but doesn't work
1ΒΊ=FIND("0";B2)
2ΒΊ=FIND(CHAR(1);SUBSTITUTE(B2;"0";CHAR(1);2))
Number
Position
[Code].....
Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies View RelatedTo 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
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.
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
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: .....
Here is example... Explanation is in the title
Book2.xls
I would like to end a macro with a specific cell (e.g. E50) in the first cell in the upper left hand corner of the screen. How might I accomplish this?
View 3 Replies View RelatedI have a user who has created a spreadsheet and wants to fix the charts in a particular position.
When she places them in one position and saves the spreadsheet they appear to have moved the next time she opens the spreadsheet.
Is there any way to fix them in a certain position without having to protect the worksheet?
In cell A2 I have text with a lot of /, so I need formula that will return the position of the before last / in the cell. (Or the last if above isnt possible).
View 9 Replies View RelatedI've written code that involves adding a number of new sheets to a workbook. I want to specify that each new sheet is positioned to the right hand side of all other sheets in the book, so that the sheets are positioned in the order (when reading from left to right) that they are added.
I know about the Copy After:= thing but I only know how to specify copying after a particular sheet name. I could solve my problem by each time referencing the sheetname from the previously-created sheet, but I think there must be a way to it properly.
I have created a button in an excel worksheet that when clicked, creates new tabs each containing a chart. I have tried to modify the code to position the chart within a specific range of cells on each tab, however, I have not been able to get it to work. Below is my code. Please see the code following 'Add the Chart.
VB:
Sub AddCharts()
Dim lastr As Long, r As Long
Dim shname As String
Dim ltitles As Range, rng As Range, rData As Range, x As Range, tbl As Range, r2Data As Range
On Error Goto ErrorCatch:
Set ltitles = Sheet1.Range("A3:N3")
Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A3").End(xlDown).Row)
[Code] ......
ErrorCatch:
'Debug.Print Err.Number
'Debug.Print Error
If Err.Number = 1004 Then
shname = x.Value & x.Row + 1
End If
If Err.Number = 20 Then Exit Sub
Resume
End Sub
Formula/code to change the position of where the subtotals are placed. I don't want them appearing at the beginning or end of the data set but in a separate column beside each data set. how to access the code so I can try and alter it myself.
View 14 Replies View RelatedA simple question:
I have a column of numbers, for example:
2
5
5
8
8
8
20
50
I would like to know the value that corresponds to (for ex.) 5th position from, of course, the beginning of the column, ie 8.
I use a Workbook with several Sheets. I want to be able to quickly move to the same cell (whatever cell is currently in use) up and down the Sheets. Ideally I would also like the chosen cell to be centered on the page as well!!
View 2 Replies View RelatedHow to find position of lust number in column (non macro preferably).
2
3
1
3
2
1
3
2
1
For number 1 match will return 3 but I need 9 (and I can't sort column opposite)
I'm am trying to find some way to determine if a particular tab falls before/after/between other tabs in a workbook. For example, if I have a tab "Top" and a tab "Bottom", I want to be able to determine if tab X is between them.
View 14 Replies View RelatedI have an array that I want to go through several times. I want to try something like this:
[Code] ......
I cant just say "If Val < 7 Then" because it will spit out the string where "Val" is.
I know that I could create another variable like so:
[Code] .....
but this doesnt seem like an elegent solution...
How to know the position of third comma in the same cell.
I am aware that you can use Match to find the position of an entry in an array, and the following code works as expected, returning an index of 4:
View 2 Replies View Relatedhave an assignment to complete and one of the tasks I found impossible to complete. First condition is that it should be completed with a formula (not VBA). So here is the task - I have different cells with IBAN codes in them. The 14th position in the IBAN is used to identify the currency of the account. There are 3 possible numbers - 0, 1 and 2 and each is for different currency. So I have this table where I need to lookup the 14th character in the cell with the IBAN, from there the formula needs not only to check what the number is (0,1 or 2) but also to compare it to another table from where to decide which is the currency. I tried various combinations with FIND/SEARCH but can't find a way to do it.. and I can't find other functions/formulas that can isolate a particular character position in a cell..
View 3 Replies View Relatedif i have an array myarray=array("MA","PA","CA")
is it possible to go down a row and if it said "MA" then return 0 or if it said CA return a 3 the number of the array position
How do I insert a row using a macro button that insert the row beneath the row that the macro button is touching?
View 9 Replies View Relatedusing excel 2003.
I have the following code to display a toolbar everytime my template is open
'Show Custom Toolbar "Trade Log" on Workbook Open
On Error Resume Next
With Application.CommandBars("Trade Log")
.Position = msoBarFloating
.Left = 5000
.Top = 600
.Visible = True
End With
End Sub
(hope i wrapped the code quotes correctly this time ))))
I noticed that when you drag a custom toolbar to the far right of your screen - it locks in to a vertical toolbar space.
How can i position my toolbar in this place automatically by modifying the above code.