Extracting date and moving it to another cell

Closed
Peaches - Jul 20, 2009 at 05:12 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 23, 2009 at 08:48 PM
Hello,

I have a cell with text , date and upc #. I want to move the date and upc# to adjacent cells. Is there a formula for this?

5 replies

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".
1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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
0
Thank you it work, but I am still confused as to how? Can you explain to me what the search function does so I will understand for myself how to use it next time other than following the instructions. Thank you so much again.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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;.
0
Thank you that is very helpful. I will try using more strings to practice.
0
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...
0

Didn't find the answer you are looking for?

Ask a question
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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)

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
0