Add text to meet criteria

[Keyrookie]

Hey all,

How can I AUTOMATICALLY add a text string to a existing string when the
existing string is not constant? I'm thinking maybe an IF function with
a CONCATENATE function? I need all strings to equal this format - 0d 00h
00m. The challenge is when copied from other files it is not constant,
ex.:

E4: 2h 39m
E6: 56m
E8: 12h 08m
E10: 5m

I need all of the above to read like this:

E4: 0d 02h 39m
E6: 0d 00h 56m
E8: 0d 12h 08m
E10: 0d 00h 05m

I need the formula to read the string in the cell(s) and add whatever
string is necessary to meet the desired result of 0d 00h 00m (as
described above). If the cell is already reading 0d 00h 00m then no
action is required. I've tried other ideas and have had other posts
with a different approach but none seem to work. I'm thinking this
might be an easier problem to solve. After my text reads like above I
use the RIGHT & LEFT functions to extract the digits so I have just the
days, hours, minutes remaining so I can then add them to NOW() to know
when our deadlines are due.

Thanks in advance for your help,

Keyrookie

 

[Pete_UK]

This seems to cater for all eventualities, assuming only one digit for
days and no leading or trailing spaces in your string (if you are
likely to have any unwanted spaces then you will need to have TRIM(E4)
for every occurence of E4 in the formula):

=IF(ISNUMBER(SEARCH("d",E4)),LEFT(E4,3),"0d
")&IF(ISNUMBER(SEARCH("h",E4)),IF(SEARCH("h",E4)=2,"0"&MID(E4,SEARCH("h",E4)-1,3),IF(SEARCH("h",E4)=LEN(E4),MID(E4,SEARCH("h",E4)-2,4)&"
",MID(E4,SEARCH("h",E4)-2,4))),"00h
")&IF(ISNUMBER(SEARCH("m",E4)),IF(SEARCH("m",E4)=2,"0"&MID(E4,SEARCH("m",E4)-1,3),MID(E4,SEARCH("m",E4)-2,4)),"00m")

This is all one formula looking at cell E4 - copy down to other cells
as necessary. Be wary of spurious line-breaks in the newsgroups, which
often introduces hyphens.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

What you are asking (to change the contents of the cell where the data is
entered) can't be done by a formula in that cell (entering the data would
overwrite the formula). You can do it with VBA code though. Right click the
sheet tab (at the bottom of the sheet) where these entries are going to be
and copy/paste the code located after my signature into the code window that
appears. Now, whenever you make an entry in Column E, it will be parsed
according to your rule or, if the entry can't be coerced into that format,
an error message will appear.

I notice that your example shows only even numbered cells (without
describing a limit to the number of cells). Right now, my code will parse an
entry into any cell in Column E; if Column E can have other data in
different formats, I will need to modify my code to handle them. If this is
the case, please describe exactly which cells need to be parsed so that I
can modify the code to handle only them.

Rick

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DHM(0 To 3) As String
Dim Contents As String
Dim Parts() As String
If Target.Column = 5 And Target.Value <> "" Then
Application.EnableEvents = False
DHM(1) = "0d"
DHM(2) = "00h"
DHM(3) = "00m"
Contents = Trim$(LCase$(Target.Value))
Do While InStr(Contents, " ")
Contents = Replace(Contents, " ", " ")
Loop
If InStr(Contents, " ") Then
Parts = Split(Contents)
DHM(InStr("dhm", Right$(Parts(0), 1))) = Right$("00" & Parts(0), 3)
If UBound(Parts) = 1 Then
DHM(InStr("dhm", Right$(Parts(1), 1))) = Right$("00" & Parts(1), 3)
ElseIf UBound(Parts) = 2 Then
DHM(InStr("dhm", Right$(Parts(1), 1))) = Right$("00" & Parts(1), 3)
DHM(InStr("dhm", Right$(Parts(2), 1))) = Right$("00" & Parts(2), 3)
End If
If DHM(0) = "" Then Contents = DHM(1) & " " & DHM(2) & " " & DHM(3)
End If
If Contents Like "*#d ##h ##m" And Left$(Contents, 1) Like "#" Then
On Error GoTo Damn
Application.EnableEvents = False
Target.Value = Contents
Else
MsgBox "The contents of " & Target.Address & " are malformed!"
End If
End If
Damn:
Application.EnableEvents = True
End Sub