Help with Excel 2003 formula...

[ToneDeff]

Anyone good with Excel about ?

Im trying to sort out alot of text by the amount of characters it contains per cell, and merge if they are under certain amounts.

giving me a real headache figuring out how to do it

think the easiest way to explain is to attach an example of what i'm trying to do..

if "text 1" is over 50 characters i need to be alerted and then manually edit it, have got this bit sorted using conditonal formating + a LEN cell.

i then need a single formula to somehow...

if "C4" is over 50 char don't copy to cell "I4"
if "C4" + "E4" = less then 46 characters copy "C4" to cell "I4" and append "E4" seperated with "; at " / e.g "C4; at E4"
if "C4" is under 50 and adding "E4" makes the total char = over 46 just copy "C4" to "I4"

included examples of outputs i'm trying to get in excel file.

not sure if this is possible or not, only ever used excel before to play the flight sim game

p.s
the cell's numbers i used are just examples i need to be able to click drag down spreadsheet, sorry don't know proper terms ;P

p.p.s
sorry for another newb question but, is theer a formula to remove comma from start of cell if it exists.

i found a way to delete first character of cell but not only if it's a comma's...
or is there a way to make find and replace to only look at the first character of cell ?

 

[ToneDeff]

if "C4" + "E4" = less then 46 characters copy "C4" to cell "I4" and append "E4" seperated with "; at " / e.g "C4; at E4"
if "C4" is under 50 and adding "E4" makes the total char = over 46 just copy "C4" to "I4"

just realised both those checks should be 45 not 46

 

[techquest]

Will look into this for you.

Right now to remove your commas use this as a macro/vbs

Sub RemoveCommas()
Dim cell As Range
For Each cell In Selection
cell = Trim(WorksheetFunction.Substitute(cell, ",", ""))
Next
End Sub

Record it and then select your cells, run macro, and it will remove the commas for you. If your not sure how to do a macro then go to help quickstart macro and it will point you in the right direction.

 

[ToneDeff]

Will look into this for you.

Right now to remove your commas use this as a macro/vbs

Sub RemoveCommas()
Dim cell As Range
For Each cell In Selection
cell = Trim(WorksheetFunction.Substitute(cell, ",", ""))
Next
End Sub

Record it and then select your cells, run macro, and it will remove the commas for you. If your not sure how to do a macro then go to help quickstart macro and it will point you in the right direction.

thanks i started looking into macro's earlier by just recording myself doing random things then looking how it's recorded

would that macro remove all comma's from cells or just one from the start of cell if it exists ?

i made a "replce " Macro by recording myself doing a search n replace, but then realised it was removing all comma's form cell and not just the one from start.

 

[ToneDeff]

got the merging cells working with a few nested "IF"... just stuck with the commas now

need something like > if first character is "," then remove it..

 

[techquest]

thanks i started looking into macro's earlier by just recording myself doing random things then looking how it's recorded

would that macro remove all comma's from cells or just one from the start of cell if it exists ?

i made a "replce " Macro by recording myself doing a search n replace, but then realised it was removing all comma's form cell and not just the one from start.

It would remove all. But will look into things further for you.

 

[ToneDeff]

It would remove all. But will look into things further for you.

no worries anymore, i found a macro to clean leading char

 

[techquest]

Record a macro and name it Remove1stComma

enter text in blue into the macro window

Sub Remove1stComma()
'
' Remove1stComma Macro
' removes 1st comma

Dim LR As Long, cell As Range
LR = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:B100" & LR)
If Left(cell.Text, 1) = "," Then cell = Format(Mid(cell.Text, 2), "@")
Next cell

End Sub

where code reads "For Each cell In Range("A1:B100" & LR)"

make your range ("A1:???" & LR) (where ??? = last cell you want to look at in your worksheet)

This code will remove only the 1st comma in each cell in range.

 

[ToneDeff]

Record a macro and name it Remove1stComma

enter text in blue into the macro window

Sub Remove1stComma()
'
' Remove1stComma Macro
' removes 1st comma

Dim LR As Long, cell As Range
LR = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:B100" & LR)
If Left(cell.Text, 1) = "," Then cell = Format(Mid(cell.Text, 2), "@")
Next cell

End Sub

where code reads "For Each cell In Range("A1:B100" & LR)"

make your range ("A1:???" & LR) (where ??? = last cell you want to look at in your worksheet)

This code will remove only the 1st comma in each cell in range.

thanks for that but i've ended up doing it via a formula

You don't have permission to view the code content. Log in or register now.

Removes leading comma if exists and then trims any extra "spaces"

if no comma at start still removes any extra "spaces", but leaves rest as is.

 

[techquest]

Good to see your getting there.