TekBytz

Windows

How to find a substring between Nth and (N+1)th occurance of a charecter in a cell in Excel

by on Jun.21, 2013, under Office, Windows

Excel’s built-in functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the  string beween nth and n+1th charecter occurrence of specified data. Fortunately, there are ways to make Excel do this. The function used could be complex but could be accomblished.

Consider if you have the text “How to find the substring in Excel” in cell A1 and incase if you need to find 5th word in the string the below formula can be used. This can be used for strings with any seperators

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1)))

 As a Generic formula the below paramters can be changed to achive to find any word between occurances

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1)))

 Where

A1 – Cell of the string

C – is the Charecter occurance, can be ” “, “.” etc

n – Nth occurance of Charecter C, n starts from 0

m – N+1 occurance of Charecter C, m starts from 0

 Few other useful formulas that would handle strings.

Finding the Nth position of a charecter

=FIND(CHAR(1),SUBSTITUTE(A1,”i”,CHAR(1),3))

This formula will inf the 3rd occurance if charecter “i” in cell A1.

Alternte formula with similar function

=SEARCHB("i",A1,(SEARCHB("i",A1,(SEARCHB("i",A1,1)+1))+1))

Finding the first Word in a String

=IF(ISERR(MID(A1,1,FIND(" ",A1)-1)),"",MID(A1,1,FIND(" ",A1)-1))

This formula will find the 1st word in a sting in cell A1.

Leave a Comment :, more...

Recreating corrupted wireless network profile in Vista

by on Sep.03, 2010, under Windows

If Windows Vista prompts you to enter the Wirless password everytime after reboot even after you save the password, you might want to know something is wired with Windows Wireless Management.  Wireless Management is not saving your passwords and matter of fact not even the profile. You can verify it by going to Network and Sharing > Manage Wireless Networks option.

This means your Wireless profile setting is corrupt and it is better to recreate so that Windows Vista starts saving the wireless profile and password. Follow below steps to recreate the profiles.

  1. Goto Windows Services and Stop WLAN AutoConfig service.
  2. In regedit find the following key and delete all the sub keys under it.
    HKEY_LOCAL_MACHINESOFTWAREMicrosoftWlansvc
  3. Now go back to Windows services and restore the WLAN AutoConfig service
  4. Add the wireless profiles again and save the password. The wireless profiles will start appearing in your Manage Wireless window.

Hope this has helped your resolve the issue. If you have any questions please let me know.

1 Comment :, , more...

Disabling compressed files (ZIP/CAB) search in Windows XP/Vista

by on Jun.11, 2009, under Windows

Windows XP / Vista have a built in feature of supporting compressed archive files. The file types supported are .zip and .cab. Windows treats these as folders and displays it in the windows explorer tree and also includes these files during the search. This would a hindrance to the users who had used their favorite compression utility like winzip, winrar or 7-zip in their previous versions of operating system. Windows file search functionality will be slow when there are lots of compressed files in the search folder. We can overcome this hurdle by disabling this feature. This can be later enabled when required.

For Window XP to Disable

1. Open start > Run

2. Type the command regsvr32 %systemroot%zipfldr.dll /u

3. Press ok on the popup message

4. The change will take effect immediately, but you may have to restart Windows for all features of the built-in ZIP support to disappear

For Windows XP to Enable

1. Open start > Run

2. Type the command regsvr32 %systemroot%zipfldr.dll /u

3. Press ok on the popup message

4. The change will take effect immediately, but you may have to restart Windows for all features of the built-in ZIP support to be available

Where %systemroot% normally is C:windowssystem32

For Windows vista to Disable

1. Open Start > Run

2. Type “regedit.exe and click ok

3. Navigate to the key HKEY_CLASSES_ROOTCLSID

4. Export the values under the key HKEY_CLASSES_ROOTCLSID{E88DCCE0-B7B3-11d1-A9F0-00AA0060FA31} and HKEY_CLASSES_ROOTCLSID{0CD7A5C0-9F37-11CE-AE65-08002B2E1262}

(This process is to backup those keys incase later if this feature needs to be enabled)

5. Delete the keys HKEY_CLASSES_ROOTCLSID{E88DCCE0-B7B3-11d1-A9F0-00AA0060FA31} and HKEY_CLASSES_ROOTCLSID{0CD7A5C0-9F37-11CE-AE65-08002B2E1262}

(The key keys HKEY_CLASSES_ROOTCLSID{E88DCCE0-B7B3-11d1-A9F0-00AA0060FA31} is associated with the zip file and the key

HKEY_CLASSES_ROOTCLSID{0CD7A5C0-9F37-11CE-AE65-08002B2E1262} is associated with cab files. In case you need to disable one of the feature zip/cab you can do it by deleting the corresponding keys.)

6. Restart the computer for the changes to take effect.

Alternate Method:

1. Download the reg file here

2. Double click to install

3. It is successful, restart the computer for the changes to take effect.

For Windows Vista to Enable

1. Open Start > Run

2. Type “regedit.exe and click ok

3. Go to File > Import and select the files that are taken backup in step 4 (For Windows vista to Disable).

4. On successful import, restart the computer for the changes to take effect.

Alternate Method:

1. Download the reg file here

2. Double click to install

3. It is successful, restart the computer for the changes to take effect.

Leave a Comment :, , , more...

Customizing / Automating windows installation with nLite/vLite

by on Oct.26, 2008, under Windows

Installation of windows will not be a new thing for one who owns a laptop/computer with Windows OS running on it. This comes true when coming to re-installation too. Here is a way where you can make your installation much easier. A freeware tool nLite or vLite can be used to customize your windows installation to make it easier.

Advantages of nLite and vLite:

  1. These software are freewares.
  2. Windows service packs can be slipstream to the original installation disk
  3. The hot fix or patches can be integrated.
  4. Drivers can be integrated
  5. Customized boot can be created.]
  6. Installation can be made unattended by giving default values.
  7. Windows installation components can be added / removed.
  8. Registry tweaks can be done before installation.
  9. Burn it into a bootable image file or CD.
  10. Saves installation time and reduces manual intervention

Freeware usage:

nLite -> Windows 2000, Windows XP, Windows 2003

vLite -> Windows Vista, Windows 2008

*All editions of the above OS is supported.

How to use it?

  1. Download nLite or vLite from their official websites.
  2. Install the nLite/vLite package.
  3. Choose the location of original windows installation disk.
  4. Choose the modification that needs to be done in windows installation in the tabs Integrate/Remove/Setup/Create.
  5. Provide all the inputs as a part of customization.
  6. Finally burn it to CD/DVD.
  7. Use it for your future installation.

Note: The developers claim that not to get support from Microsoft for any installation errors which is created by nLite/vLite.

Leave a Comment :, , , , more...

Connecting to a hidden administrative default share in Windows Vista

by on Oct.11, 2008, under Windows

In windows vista by default the hidden default shares like c$, ADMIN$, IPC$ and Print$ are disabled due to User Account Control (UAC) feature avoiding it to be connected in the network. So when you try to connect from other computers in the network you might possibly get the below errors.

Logon unsuccessful:
Windows is unable to log you on.
Make sure that your user name and password are correct.

You may get the below error when you try to connect using “Net Use” command:

System error 5
has occurred. Access is denied.

How to get rid of it?

There are 2 solution for this problem

  1. You can create an explicit share of the required device.
  2. You can modify the registry to enable LocalAccountTokenFilterPolicy key.

Creating explicit share for the device:

  1. Open Explorer
  2. Choose the folder or drive
  3. Right click on the folder or drive and click on share
  4. If you have password protected sharing enabled, select which users can access the shared folder and their permission level. To let all users have access, select Everyone in the list of users. By default, the permission level is “Reader.” Users who have this permission level cannot change files or create new files in the share. To let a user change files, change folders, create new files, and create new folders, use the “Co-owner” permission level.
    If you have password protected sharing disabled, select the Guest account or the Everyone account. This is the same as simple sharing in Windows XP.
  5. Click on share and then press OK

This method is safe and can be done by beginners.

Modify registry to enable LocalAccountTokenFilterPolicy Key:

Here is also an alternate which requires some expertize in the operating system. Here are the steps.

  1. In Run prompt type regedit to open registry editor
  2. Browse through the following sub key
    HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionPoliciesSystem
  3. Create a new entry with the below information
    Key Name : LocalAccountTokenFilterPolicy
    Key Type : DWORD (32-bit)
    Key Value : 1
  4. Exit registry editor.

To disable this option modify the registry key value to 0.

Warning: If you are trying out this method user need to have a knowledge on windows registry. If some of the keys are modified incorrectly it may cause a serious problem to the operation system for proper functioning. Backup registry before doing any changes.

1 Comment :, , , , more...

Disconnecting a hidden remote connection

by on Sep.21, 2008, under Windows

One fine morning I was surprised to see the error “Multiple connections to a server or shared resource by the same user using more than one user name are not allowed” when I was trying to connect a network drive. I  verified for network connection in  windows explorer but I could not find any connections. Here is a way to solve it.

1. Goto command prompt.
2. Type “NET USE” (with out codes) to view all the connections active or inactive.


Status       Local     Remote                    Network
-------------------------------------------------------------------------------
Unavailable  M:        \192.168.0.100Music     Microsoft Windows Network
OK                     \192.168.0.103m$        Microsoft Windows Network
The command completed successfully.

3. Type “NET USE \<remote_resource_name>” (with out codes and replace <remote_resource_name> with the actual name) to display the complete details.


Local name        Z:
Remote name       \<remote_resource_name>
Resource type     Disk
Status            OK
# Opens           2
# Connections     1
The command completed successfully.

4. To disconnect this resource use the command without codes as “NET USE \<remote_resource_name> /DELETE
5. Now you are available to connect the remote resource.

Here are the other ways to disconnect through GUI if the connection is visible.

If the connection is mapped to the drive letter.
1. Open the Windows explorer
2. Goto Tools and choose the option Disconnect Network Drive
3. Choose the drive letter and click Disconnect.

If the connect is not mapped to the drive letter.
1. Right click on MyComputer
2. Choose Disconnect Network Drive and it display the all the resources connected
3. Choose the resource name or the drive letter and click OK

1 Comment :, , more...

Reseting Windows 9x/2000/XP administrator password

by on Aug.30, 2008, under Windows

Forgetting things in life is more common, but some somethings you will not have any impact. Imagine you have forgot a windows administrator password. You are really into trouble. You can’t really find any one to make you to get into your system. But here is a way to reset it.

If you are the old version of windows the the problem is much simple. To reset a password in windows 98, there is nothing much complex since the there is not much tight security.

For Windows 9x

1. Boot the system in dos mode
2. Go the windows directory
3. Search for *.PWD file
4. Delete the *.PWD file.
5. Reboot it and now you are in with the blank password.

But on later versions of the windows the security features are made so complex that it involves some complex steps to reset your password in case you forget it.

For Windows 2000/XP

1. Get back you windows bootable CD. Boot it.

2. Proceed to the the screens until you get the option to a) install windows or b) repair windows

3. Press ‘R’ to repair windows and then choose the appropriate options until reboot.

4. Your machine will be back in windows GUI mode. You need to enter to the command prompt by pressing “Shit + F10”. This step differs for windows 2000 and XP.

  • For windows 2000 press “shift + F10” when you see the screen “Registering Components”
  • For windows XP press “shift + F10” when you see the screen “Installing devices”

5. Now you are in the command prompt. From here you need to invoke the control panel.

  • For Windows 2000, Type “control.exe” in command prompt
  • For Windows XP, Type “nusrmgr.cpl” in command prompt

6. Now you could be able to access control panel.
7. From there choose user management to reset your password.
8. Once done, close the control panel and allow the repair process to get completed.
9. Finally reboot your computer
10. Now you can be in with your new password.

Here is an alternate way too if you have a free distribution of Linux know as Knoppix. Here are the steps.

1. Download the free version of Linux from http://www.knoppix.org/
2. Download the a package to change the windows password from here
3. Install the package chntpw as below

Knoppix@ttyp1[knoppix]$ alien –to-tgz chntpw_.deb
Knoppix@ttyp1[knoppix]$ tar xvzf chntpw.tgz ./usr/sbin/chntpw
Knoppix@ttyp1[knoppix]$ mv ./usr/sbin/chntpw ./

4. Find the windows partition using the command

cat /etc/fstab

5. Mount the windows NTFS partition using the command

mount -o rw /dev/hdax /mnt/windows

    x – replaces with the numeric depending on the configuration

6. Change your directory where the SAM file is available

cd /mnt/windows/WINDOWS/system32/config

7. Run the command

chntpw -u administrator SAM system SECURITY

8. This will ask you to enter a new password for the administrator account.
9. Just hit enter so that blank password will be entered.
10. Write the changes when it prompts to do so.
11. Reboot your machine on your hard drive and log in to windows with blank password.

NB: The author is not responsible for any kind of misuse by the readers.

Leave a Comment :, , , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!