Thank you for all of your help
Thank you for all of your help
Last edited by LenaK; 01-31-2015 at 12:21 PM.
If you data is in A1 try this and pull down:
Formula:Please Login or Register to view this content.
Thanks,
Do you know how to accomplish the same with VBA?
Assuming your text is in A1, in B1 enter =IF(ISERROR(SEARCH(":",A1)),LEFT(A1,4)&":"&RIGHT(A1,LEN(A1)-4),A1)
UDF?Please Login or Register to view this content.
recalling this message
Last edited by LenaK; 01-31-2015 at 11:59 AM.
Edit : too late …
Thank you,
Could you give an example how to use this function when applying to column A for example. Starting in row 4 and moving down to the last row with data?
I've used same formula as above.
Select cell or range where you want to change this and press ctrl+shift+i (i as insert).
Please Login or Register to view this content.
What is .Address?
Also what is 255?
I think I understand the rest.
Thanks,
Instead of using fixed range it takes current selection.
Address is for current cell same as above is A1.
255 is part of MID function.
Formula:Please Login or Register to view this content.
MID will take of current cell (A1 or .Address) start number (5th) and it will take 255 characters.
If you have longer words you need to extend this number but mostly not needed.
Try, no loop
Please Login or Register to view this content.
Thank you, it works without the loop!
If cell="" then leave cell = ""
If Len(cell)<5, leave cell as it is.
If Len(cell)>5 and Mid(cell,5,1)=":", means cell already has ":" at 5th position, do nothing.
If Len(cell)>5 and Mid(cell,5,1)<>":" then insert ":" at 5th position.
HTH
awesome thanks!
Is there an easy way of finding and removing characters from a string with code?
If string contains (anywhere) either ";" or "w" remove them?
I also would like to know which of these methods would be faster if a sheet contained very many columns and rows of data?
Thanks
You can just Find and Replace.
Like
Please Login or Register to view this content.
what is 2?
xlPart..........
If it doesn't fine "w" or ";" there will be no error message, correct?
Correct............
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks