Extracting date and moving it to another cell
Closed
Peaches
-
Jul 20, 2009 at 05:12 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 23, 2009 at 08:48 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 23, 2009 at 08:48 PM
Related:
- Extracting date and moving it to another cell
- How to insert picture in word without moving text - Guide
- Excel arrow keys not moving cells - Guide
- If cell contains date then return value ✓ - Excel Forum
- Excel formula to check if cell contains a date - Excel Forum
- Excel formula to subtract one cell from another - Excel Forum
5 responses
I used the right formula to remove the upc# and it worked. I tried the mid formula, but it did not work. The date is formatted like so "songs by josephh, joseph (60909) 0055677186".
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 20, 2009 at 08:24 PM
Jul 20, 2009 at 08:24 PM
how is it configured. where is the date portion. you can use mid function or left or right function.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 21, 2009 at 08:12 PM
Jul 21, 2009 at 08:12 PM
I am going to structured solution.
suppose A1 has this entry
songs by josephh, joseph (60909) 0055677186
in B1 type the formula
=SEARCH("(",A1,1)
in C1 type this formula
=SEARCH(")",A1)
in D1 type this formula
=MID(A1,B1+1,C1-B1-1)
you will get
60909
is this what you want
of course you can combine the formula in B1 and C1 into D1. But I am doing this so that you can understand the process of this formula
for upc# you have already succeeded
suppose A1 has this entry
songs by josephh, joseph (60909) 0055677186
in B1 type the formula
=SEARCH("(",A1,1)
in C1 type this formula
=SEARCH(")",A1)
in D1 type this formula
=MID(A1,B1+1,C1-B1-1)
you will get
60909
is this what you want
of course you can combine the formula in B1 and C1 into D1. But I am doing this so that you can understand the process of this formula
for upc# you have already succeeded
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 22, 2009 at 08:13 PM
Jul 22, 2009 at 08:13 PM
Yes. do not hesitate to ask questions.
search functions finds the where some sub string occurs in a string
suppose in A1 the entry is
songs by josephh, joseph (60909) 0055677186
=search("(",a1) gives 26
that means the first "(" occurs on the 26th character (count including spaces)
there is a third argument here suppose 1 which means occurrence after 1st character
suppose there is another (" after the first "(" and if you want to find that position then you type
=search("(",a1,27) or if you want to be more sophisticated
=search("(",a1,search("(",a1))+1))
I am sure you know of "mid" function ( and also left and right)
I hope this explanation will be of help to you. greetings
if you do some exercises of small strings you can get the hang of it;.
search functions finds the where some sub string occurs in a string
suppose in A1 the entry is
songs by josephh, joseph (60909) 0055677186
=search("(",a1) gives 26
that means the first "(" occurs on the 26th character (count including spaces)
there is a third argument here suppose 1 which means occurrence after 1st character
suppose there is another (" after the first "(" and if you want to find that position then you type
=search("(",a1,27) or if you want to be more sophisticated
=search("(",a1,search("(",a1))+1))
I am sure you know of "mid" function ( and also left and right)
I hope this explanation will be of help to you. greetings
if you do some exercises of small strings you can get the hang of it;.
Hello,
I have another question. I have two worksheets. One worksheet tracks the sale of items and list the monitary amount of those sales. The other worksheet list the product and date to be sold. I want to link the worksheets so that when the sales on an item reach a certain dollar amount , the sell date on the other sheet will change to red. Is this possible...
I have another question. I have two worksheets. One worksheet tracks the sale of items and list the monitary amount of those sales. The other worksheet list the product and date to be sold. I want to link the worksheets so that when the sales on an item reach a certain dollar amount , the sell date on the other sheet will change to red. Is this possible...
Didn't find the answer you are looking for?
Ask a question
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 23, 2009 at 08:48 PM
Jul 23, 2009 at 08:48 PM
your use two terms "item" and "product". I take it they are the same
your sheet named sheet1 is something like this(a1 down)
product amount
a 1
s 2
d 3
f 4
sheet2 is like this
product date
f 8/1/2009
d 8/2/2009
s 8/3/2009
a 8/4/2009
right click sheet1 tab(repeat sheet1 tab) and click view
in the window that comes up copy this "event handler "(something like a macro but pertaining to a particular sheet and the macro automatically runs if a condition is met)
I have condition as the amount is greater than 25(8th line in the code) . you can modify it to suit you in your file
copy this experimental file(copy sheet 1 and sheet 2 in a new file). now change any amount (that is any cells in column B of sheet1 from B2 down) into some number more than 25 and see what happens in sheet2. the date of the corresponding product is colored red. change another amount in sheet1 into more than 25 and see what happens in sheet2.
if this is what you want do the same thing to your original file.
post feedback particularly if there is any bug
your sheet named sheet1 is something like this(a1 down)
product amount
a 1
s 2
d 3
f 4
sheet2 is like this
product date
f 8/1/2009
d 8/2/2009
s 8/3/2009
a 8/4/2009
right click sheet1 tab(repeat sheet1 tab) and click view
in the window that comes up copy this "event handler "(something like a macro but pertaining to a particular sheet and the macro automatically runs if a condition is met)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Double, y As String, cfind As Range
On Error Resume Next
If Target.Column <> 2 Then Exit Sub
If Target = "" Then Exit Sub
x = Target.Value
y = Target.Offset(0, -1).Value
If Target > 25 Then
With Worksheets("sheet2").Columns("A:A")
Set cfind = .Cells.Find(what:=y, lookat:=xlWhole)
If cfind Is Nothing Then
MsgBox "this product is not avaiable in sheet2"
GoTo line1
End If
cfind.Offset(0, 1).Interior.ColorIndex = 3
End With
End If
line1:
End Sub
I have condition as the amount is greater than 25(8th line in the code) . you can modify it to suit you in your file
copy this experimental file(copy sheet 1 and sheet 2 in a new file). now change any amount (that is any cells in column B of sheet1 from B2 down) into some number more than 25 and see what happens in sheet2. the date of the corresponding product is colored red. change another amount in sheet1 into more than 25 and see what happens in sheet2.
if this is what you want do the same thing to your original file.
post feedback particularly if there is any bug
