+ Reply to Thread
Results 1 to 22 of 22

if a string doesn't contain a character insert that character.

  1. #1
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    if a string doesn't contain a character insert that character.

    Thank you for all of your help
    Last edited by LenaK; 01-31-2015 at 12:21 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: if a string doesn't contain a character insert that character.

    If you data is in A1 try this and pull down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    Thanks,

    Do you know how to accomplish the same with VBA?

  4. #4
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: if a string doesn't contain a character insert that character.

    Assuming your text is in A1, in B1 enter =IF(ISERROR(SEARCH(":",A1)),LEFT(A1,4)&":"&RIGHT(A1,LEN(A1)-4),A1)

  5. #5
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: if a string doesn't contain a character insert that character.

    UDF?
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    recalling this message
    Last edited by LenaK; 01-31-2015 at 11:59 AM.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Post Re: if a string doesn't contain a character insert that character.


    Edit : too late …

  8. #8
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    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?

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: if a string doesn't contain a character insert that character.

    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.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    What is .Address?

    Also what is 255?

    I think I understand the rest.

    Thanks,

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: if a string doesn't contain a character insert that character.

    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: copy to clipboard
    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.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: if a string doesn't contain a character insert that character.

    Try, no loop
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    If it finds ":" it moves one cell down, correct?

    Quote Originally Posted by jindon View Post
    Try, no loop
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    Thank you, it works without the loop!

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: if a string doesn't contain a character insert that character.

    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

  16. #16
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    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?

  17. #17
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    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

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: if a string doesn't contain a character insert that character.

    You can just Find and Replace.
    Like
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    what is 2?

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: if a string doesn't contain a character insert that character.

    xlPart..........

  21. #21
    Registered User
    Join Date
    01-29-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: if a string doesn't contain a character insert that character.

    If it doesn't fine "w" or ";" there will be no error message, correct?

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: if a string doesn't contain a character insert that character.

    Correct............

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] find the character position in a string of the last occurrence of a nominated character
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 11:52 PM
  2. Replies: 3
    Last Post: 11-08-2012, 05:01 PM
  3. Insert a character into a string
    By John Brooks in forum Excel General
    Replies: 2
    Last Post: 12-23-2011, 05:03 AM
  4. Insert Character in a String
    By Laguna in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2005, 09:05 AM
  5. [SOLVED] Function to return Character Position of Xth character within a string
    By Andibevan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-09-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1